- Power Query ви позволява да консолидирате множество файлове от локална или облачна папка, стига да споделят една и съща схема на данни.
- Използването на примерен файл улеснява прилагането на групови трансформации, като например премахване на горни редове или промяна на заглавки.
- Има ограничения, когато колоните не съвпадат точно, което изисква ръчно почистване или използване на VBA скриптове за сложни случаи.
Представете си, че имате куп месечни или седмични отчети, разпръснати по папки, и вместо да губите сутринта си в трескаво копиране и поставяне на данни, можете да обедините всичко в една електронна таблица на Excel само с няколко кликвания. Именно тук се намесва Power Query, инструмент, който е по същество дясната ръка на всеки, който работи с данни, позволявайки ни да консолидираме файлове, които споделят една и съща структура, без усилие.
Най-готиното нещо на тази система е, че не ви ограничава до локална папка на вашия компютър; можете да изтегляте данни от сайтове на SharePoint, Azure Data Lake или Azure blobs . Ключът към безпроблемната работа е файловете да имат една и съща схема, което означава, че колоните съвпадат, въпреки че Power Query е достатъчно интелигентен, за да помогне за почистването на данните, ако съдържат шум или ненужни редове.
Процесът на комбиниране стъпка по стъпка
Когато се свържете с източник на данни, първото нещо, което ще видите, е изгледът на файловата система. Тук най-важната колона е „Съдържание“, която съдържа двоичното представяне на всеки документ. Ако щракнете върху една от тези двоични стойности, програмата ще се опита да определи дали е CSV, JSON или Excel файл, за да го отвори. За да комбинирате целия пакет, имате две опции: щракнете директно върху бутона „Сливане“ или, ако сте по-прецизни, изберете „Трансформиране на данни“, за да филтрирате списъка с файлове (например, за да игнорирате конкретни папки), преди да продължите със сливането.
След като влезете в диалоговия прозорец за сливане, Power Query използва примерен файл, за да анализира структурата . По подразбиране той избира първия файл в списъка, но можете да го промените, ако има друг, който по-добре представя набора от данни. Тук можете да изберете опцията за пропускане на файлове с грешки , което е спасително средство, когато даден отчет е повреден или празен, предотвратявайки спирането на целия процес.
Управление на изхода и почистване на данни
След приемане на сливането, системата извършва някаква техническа магия зад кулисите: създава примерна заявка и параметризирана функция. Това означава, че всички промени, които правите в примерния файл, ще бъдат автоматично репликирани към всички останали файлове в папката. Ако например вашите CSV файлове имат четири реда ненужни заглавки преди действителните данни, можете да отидете на заявката за трансформация и да използвате опцията за премахване на горните редове , за да почистите този хаос.
След почистване на редовете е важно да приложите функцията „ Използване на първия ред като заглавки“ . Имайте предвид, че когато направите това, Power Query обикновено добавя стъпка за автоматично преобразуване на типа данни. Ако при връщане към основната заявка видите грешка, че колоната „Колона1“ не може да бъде намерена, не се притеснявайте; това е нормално, защото сте преименували колоните в примера и следващата стъпка вече не разпознава старите имена. Просто изтрийте тази грешна стъпка от екрана „Приложени стъпки“ и всичко ще се върне към нормалното.
Често срещани предизвикателства и усъвършенствани алтернативи
Понякога животът не е толкова прост и се натъкваме на файлове, които не са идентични. Например, ако имате отчети на ServiceNow, където единият заглавен файл се нарича „resolved_on“, а другият „closed_on“, Power Query може да ви създаде проблеми, защото изисква последователна структура . Ако примерният файл не съвпада точно с останалите, инструментът може да пропусне информацията от несъответстващите файлове или да възникне грешки при импортиране.
За случаите, когато структурата е напълно хаотична или обемът на данните е огромен (надвишаващ един милион реда в Excel), има други опции. Някои потребители избират VBA скриптове, използващи CSV интерфейси , които позволяват свързването на файлове без повтаряне на заглавките, независимо дали разделителят на полетата или записите варира от файл до файл. Въпреки че Windows Shell е изключително бърз, той често се проваля при обработката на вградени разделители на редове, докато стабилното VBA решение не претоварва RAM паметта и може лесно да обработва милиони записи.
За да завършите процеса, след като данните бъдат комбинирани в Power Query, не забравяйте да зададете правилния тип данни на всяка колона (Дата за дати, Валута за доход и т.н.), преди да заредите окончателната таблица. Можете да проверите дали всичко е правилно, като филтрирате колоната „Име на източника“, за да се уверите, че всички файлове в папката са обработени успешно.
Възможността за автоматизиране на обединяването на множество CSV файлове ви позволява да преминете от досадна ръчна работа към професионален работен процес, независимо дали чрез използване на гъвкавостта на Power Query за почистване на данни или чрез прибягване до VBA, когато структурите са несъвместими или обемите надвишават ограниченията на електронните таблици.