В практиката на всеки репортер (човек, който изготвя репорти) много често се среща следния казус: Има в една папка натрупани еднотипни файлове, които трябва да се обединят в един, за да се направи справка, анализ и каквото там се изисква. Решаването на тази задача е изключително лесно с помощта на Power Query From Folder.
Нека да вземем прост пример – в една папка имам четири файла с имена на различни държави. Във всеки от тях има таблица с имена на няколко футболни отбора от тази страна, от кой град са, кога са основани, как се казва стадионът, на който играят и с капацитет за колко места е той. Задачата ни е да съберем тези таблици в една, като добавим колона за името на страната. Обърнете внимание, че освен xlsx файловете с данните, които ни интересуват, в тази папка има и други файлове.
Базов случай при използване на Power Query From Folder
В базовия случай всеки файл съдържа работен лист с точно определено име (в случая Sheet1), в който се намира таблицата с данните. Всяка таблица има различен брой редове, но колоните са един и същ брой и с еднакви имена
Започваме като в Ексел отиваме на Get Data > From File > From Folder (версия 2016 и нагоре. За по-ранните версии трябва да търсите Power Query> From File > From Folder)
Отваря се този прозорец, където от Browse навигираме до папката, в която са файловете и даваме ОК
В това меню щракаме Transform Data
И това зарежда в Power Query таблица с малко странен вид. Първата колона се казва Content и в нея е съдържанието на всеки един файл (тук е нужно да отворя една скоба, за да кажа, че в Power Query в една клетка може да има не само стойност, но и цяла таблица и дори съвкупност от таблици. Но за това – друг път). Останалите колони съдържат най-различна информация за всеки файл – името му, кога е създаден, кога е променен, в коя папка се намира и т.н.
Колоната Extension показва какъв тип е файлът и можем да я използваме, за да филтрираме само тези, които са с разширение xlsx.
След като сме филтрирали нужните ни файлове трябва да натиснем бутона с двете стрелки отдясно на заглавието на колоната Content.
Тук маркираме Sheet1 и даваме ОК
И готово – Power Query е събрал всички данни в една таблица, като в първата колона е добавил името на всеки файл, което можем да използваме за да определим страната. Е, малко ще трябва да пообработим тази колона с помощта на Replace, за да махнем разширението, но ще спра до тук.
Сега нека да разгледаме по-сложния случай, в който
Файловете съдържат работни листове с различни имена
На картинката по-долу виждате, че шийтовете имат различни имена и дори някои са на кирилица:
В този случай методът, който описах по-горе, ще даде грешка. За да я избегнем, когато стигнем до тук:
Не трябва да натискаме двете стрелки до Content, а да отидем на Add Column > Custom Column и да въведем формулата: Excel.Workbook([Content],true)
Excel.Workbook е функция в езика М, която извлича съдържанието на даден ексел файл. А резултатът е, че Power Query добавя нова колона, в която всяка клетка съдържа таблица (нали ви казах, че може).
Имайте предвид, че по подразбиране тази колона отива най-вдясно на таблицата. Аз съм я преместил най-отпред за удобство.
Сега трябва да натиснем бутона до името на новата колона и да дадем ОК. Обърнете внимание, че съм махнал отметката на Use original column name as prefix.
Power Query добавя нови колони към нашата таблица:
Сега отново трябва да натиснем бутона с двете стрелки отдясно на Data
за да получим желания резултат:
Остава само да се премахнат излишните колони, но това са технически подробности.
Този метод е дори още по-гъвкав. Например, възможно е един от файловете да се казва Ю. Америка и вътре да има два шийта – Аржентина и Уругвай. В този случай резултатът от предпоследната стъпка ще изглежда така:
А на последната ще имаме отново точно това, което ни трябва:
(Отново отварям скоба, за да обърна внимание какво е името на стадиона на Данубио. Ако името ви звучи някак си българско, не се чудете. Доня Мария Минчев де Лазароф всъщност е българка, емигрирала в Уругвай със семейството си през 20-те години на миналия век. Двамата й сина са сред най-дейните основатели на този клуб. И когато се чудели как да го кръстят, тя предложила да го нарекат на река Дунав, т.е. Данубио на испански. Феновете на този клуб и до ден днешен считат, че са представители на България в Уругвай. Неслучайно миналата година първият куплет на „Мила Родино“ беше изписан върху фланелките на отбора.)
Това бяха двата метода за импорт на данни от много файлове в една папка в Ексел с помощта на Power Query From Folder. Първият е по-удобен и при него работата приключва със 7-8 клика на мишката. Но той работи при строгото ограничение шийтовете да са с едно и също име.
Вторият метод е по-гъвкав, но изисква повече работа, а и да помните функцията Excel.Workbook.