Съдържание
Формулиране на проблема
Имаме няколко файла (в нашия пример – 4 броя, в общия случай – колкото искате) в една папка Доклади:
Вътре тези файлове изглеждат така:
При което:
- Листът с данни, от който се нуждаем, винаги се извиква Снимки, но може да бъде навсякъде в работната книга.
- Отвъд листа Снимки Всяка книга може да има други листове.
- Таблиците с данни имат различен брой редове и може да започват с различен ред в работния лист.
- Имената на едни и същи колони в различни таблици може да се различават (напр. Количество = Количество = Кол).
- Колоните в таблиците могат да бъдат подредени в различен ред.
Задача: съберете данни за продажбите от всички файлове от листа Снимки в една обща таблица, за да може впоследствие да се изгради резюме или друг анализ върху нея.
Стъпка 1. Подготовка на директория с имена на колони
Първото нещо, което трябва да направите, е да подготвите справочник с всички възможни опции за имена на колони и тяхното правилно тълкуване:
Преобразуваме този списък в динамична „интелигентна“ таблица с помощта на бутона Форматиране като таблица в раздела Начало (Начало — Форматиране като таблица) или клавишна комбинация Ctrl+T и го заредете в Power Query с командата Данни – От таблица/диапазон (Данни — от таблица/обхват). В последните версии на Excel той е преименуван на С листа (От лист).
В прозореца на редактора на заявки на Power Query традиционно изтриваме стъпката Променен тип и добавете нова стъпка вместо нея, като щракнете върху бутона fxв лентата с формули (ако не се вижда, можете да го активирате в раздела Оценки на посетители) и въведете формулата там във вградения език на Power Query M:
=Table.ToRows(Източник)
Тази команда ще конвертира заредената в предишната стъпка източник референтна таблица в списък, състоящ се от вложени списъци (List), всеки от които на свой ред е двойка стойности Беше-стана от един ред:
Ще имаме нужда от този тип данни малко по-късно, когато масово преименуваме заглавки от всички заредени таблици.
След като завършите преобразуването, изберете командите Начало — Затвори и зареди — Затвори и зареди в… и вида на вноса Просто създайте връзка (Начало — Затвори&Зареди — Затвори&Зареди до… — Само създаване на връзка) и се върнете към Excel.
Стъпка 2. Зареждаме всичко от всички файлове както е
Сега нека заредим съдържанието на всички наши файлове от папката – засега както е. Избор на екипи Данни – Получаване на данни – От файл – От папка (Данни — Получаване на данни — От файл — От папка) и след това папката, където са нашите изходни книги.
В прозореца за визуализация щракнете върху Превръщам (Трансформиране) or промяна (Edit):
И след това разширете съдържанието на всички изтеглени файлове (двоичен) бутон с двойни стрелки в заглавието на колоната съдържание:
Power Query на примера на първия файл (Восток.xlsx) ще ни попита името на листа, който искаме да вземем от всяка работна книга – изберете Снимки и натиснете OK:
След това (всъщност) ще се появят няколко събития, които не са очевидни за потребителя, последствията от които са ясно видими в левия панел:
- Power Query ще вземе първия файл от папката (ще го имаме Восток.xlsx — виж Примерен файл) като пример и импортира съдържанието му чрез създаване на заявка Конвертиране на примерен файл. Тази заявка ще има някои прости стъпки като източник (достъп до файл) навигация (избор на лист) и евентуално повдигане на заглавията. Тази заявка може да зареди данни само от един конкретен файл Восток.xlsx.
- Въз основа на тази заявка ще бъде създадена свързаната с нея функция Конвертиране на файл (обозначено с характерна икона fx), където изходният файл вече няма да бъде константа, а променлива стойност – параметър. По този начин тази функция може да извлича данни от всяка книга, която вкараме в нея като аргумент.
- Функцията ще бъде приложена на свой ред към всеки файл (двоичен) от колоната съдържание – стъпката е отговорна за това Извикване на персонализирана функция в нашата заявка, която добавя колона към списъка с файлове Конвертиране на файл с резултати от импортиране от всяка работна книга:
- Допълнителните колони се премахват.
- Съдържанието на вложените таблици се разширява (стъпка Разширена колона на таблицата) – и виждаме крайните резултати от събирането на данни от всички книги:
Стъпка 3. Шлифоване
Предишната екранна снимка ясно показва, че директното сглобяване „както е“ се оказа с лошо качество:
- Колоните са обърнати.
- Много допълнителни редове (празни и не само).
- Заглавките на таблиците не се възприемат като заглавки и се смесват с данни.
Можете да коригирате всички тези проблеми много лесно – просто настройте заявката за конвертиране на примерен файл. Всички корекции, които правим в него, автоматично ще попаднат в свързаната функция за конвертиране на файл, което означава, че ще бъдат използвани по-късно при импортиране на данни от всеки файл.
Като отворите заявка Конвертиране на примерен файл, добавете стъпки за филтриране на ненужни редове (например по колона Column2) и повдигане на заглавията с бутона Използвайте първия ред като заглавки (Използвайте първия ред като заглавки). Масата ще изглежда много по-добре.
За да могат колоните от различни файлове да се поберат автоматично една под друга по-късно, те трябва да бъдат наречени еднакви. Можете да извършите такова масово преименуване според предварително създадена директория с един ред M-код. Да натиснем бутона отново fx в лентата с формули и добавете функция за промяна:
= Table.RenameColumns(#”Elevated Headers”, Headers, MissingField.Ignore)
Тази функция взема таблицата от предишната стъпка Повишени заглавки и преименува всички колони в него според вложения справочен списък Заглавия. Трети аргумент MissingField.Ignore е необходимо, за да не възникне грешка в тези заглавия, които са в директорията, но не са в таблицата.
Всъщност това е всичко.
Връщам се към искането Доклади ще видим съвсем различна картина – много по-хубава от предишната:
- Какво е Power Query, Power Pivot, Power BI и защо един потребител на Excel има нужда от тях
- Събиране на данни от всички файлове в дадена папка
- Събиране на данни от всички листове на книгата в една таблица