Как да използвам Power Query в Excel?
Excel Power Query се използва за търсене на източници на данни, осъществяване на връзки с източници на данни и след това оформяне на данните според нашите изисквания за анализ. След като приключим с оформянето на данните според нашите нужди, можем също да споделим своите констатации и да създадем различни отчети, използвайки повече заявки.
Стъпки
По принцип има 4 стъпки и редът на тези 4 стъпки в Power Query е както следва:
- Свързване: Първо се свързваме с данните, които могат да бъдат някъде, в облака, в услуга или локално.
- Трансформация: Втората стъпка ще бъде промяна на формата на данните според изискванията на потребителя.
- Комбиниране: В тази стъпка извършваме някои стъпки за трансформация и агрегиране и комбинираме данни от двата източника, за да създадем комбиниран отчет.
- Управление: Това обединява и добавя колони в заявка със съответстващи колони в други заявки в работната книга.
Има много супер мощни функции на Excel Power Query.
Да предположим, че имаме данни за покупките за последните 15 години в 180 файла. Сега управлението на една организация ще изисква консолидиране на числата, преди да ги анализира. Ръководството може да предприеме някой от следните методи:
- Те щяха да отворят всички файлове и да ги копират и поставят в един файл.
- От друга страна, те могат да използват разумно решение, което е да прилагат формули, тъй като е склонна към грешки.
Какъвто и метод да изберат, той съдържа много ръчна работа и след няколко месеца ще има нови данни за продажбите за добавената продължителност. Те ще трябва да направят същото упражнение отново.
Power Query обаче може да им помогне да не вършат тази досадна и повтаряща се работа. Нека разберем тази Excel заявка за мощ с пример.
Пример
Да предположим, че имаме текстови файлове в папка с данни за продажбите и искаме да ги получим в нашия Excel файл.
Както виждаме на изображението по-долу, имаме два типа файлове в папката, но искаме да получим данните само на текстови файлове във файла excel.
За да направите същото, стъпките ще бъдат:
Стъпка 1: Първо, трябва да получим данните в Power Query, за да можем да направим необходимите промени в данните, за да ги импортираме във файл на Excel.
За да направим същото, ще изберем опцията „От папка“ от менюто „От файл“, след като щракнем върху командата „Получаване на данни“ от групата „Получаване и трансформиране“ в раздела „Данни“ .
Стъпка 2: Изберете местоположението на папката чрез сърфиране.
Кликнете върху „OK“
Стъпка 3: Ще се отвори диалогов прозорец, съдържащ списъка за всички файлове в избраната папка със заглавия на колоните като „Съдържание“, „Име“, „Разширение“, „Дата на достъп“, „Дата на промяна“, „Дата на създаване“. „Атрибути“ и „Път на папката“.
Има 3 опции, т.е. комбиниране , зареждане и трансформиране на данни .
- Комбиниране : Тази опция се използва за преминаване към екран, където можем да изберем кои данни да комбинираме. Стъпката за редактиране се пропуска за тази опция и не ни дава контрол върху това кои файлове да комбинираме. Функцията за комбиниране отнема всеки файл в папката да се консолидира, което може да доведе до грешки.
- Зареждане: Тази опция просто ще зареди таблицата, както е показано по-горе на снимката, в работния лист на Excel, вместо действителните данни във файловете.
- Преобразуване на данни: За разлика от командата „Комбиниране“ , ако използваме тази команда, тогава можем да изберем кои файлове да комбинираме, т.е. можем да комбинираме само един тип файл (същото разширение).
Както в нашия случай, ние искаме да комбинираме само текстови файлове (.txt); ще изберем командата „Трансформиране на данни“ .
Можем да видим „Приложени стъпки“ от дясната страна на прозореца. Засега е направена само една стъпка, която е да се вземат подробности за файловете от папката.
Стъпка 4: Има колона с име „Разширение“, където можем да видим, че стойностите в колоната се записват и в двата случая, т.е. главни и малки букви.
Трябва обаче да преобразуваме всички стойности в малки букви, тъй като филтърът прави разлика между двете. За да направим същото, трябва да изберете колоната и след това да изберете „Малки букви“ от менюто на командата „Форматиране“ .
Стъпка 5: Ще филтрираме данните, като използваме колоната „Разширение“ за текстови файлове.
Стъпка 6: Трябва да комбинираме данни и за двата текстови файла, като сега използваме първата колона „Съдържание“. Ще щракнем върху иконата, поставена в дясната страна на името на колоната.
Стъпка 7: Ще се отвори диалогов прозорец с надпис „Комбиниране на файлове“, където трябва да изберем разделителя като „Tab“ за текстови файлове (файлове с разширение „.txt“) и можем да изберем основата за откриване на типа данни. И кликнете върху „OK“.
След като кликнете върху „OK“ , ще получим комбинираните данни от текстови файлове в прозореца „Power Query“ .
При необходимост можем да променим типа данни на колоните. За колоната „Приходи“ ще променим типа данни на „Валута“.
Можем да видим стъпките, приложени към данните, като използваме заявка за захранване от дясната страна на прозореца.
След като направим всички необходими промени в данните, можем да заредим данните в работен лист на Excel, като използваме командата „Затваряне и зареждане към“ под групата „Затваряне“ в раздела „Начало“ .
Трябва да изберем дали искаме да заредим данните като таблица или връзка. След това кликнете върху „OK“.
Сега можем да видим данните като таблица в работния лист.
И панел „Заявки за работна книга“ от дясната страна, който можем да използваме за редактиране, дублиране, обединяване, добавяне на заявки и за много други цели.
Excel Power Query е много полезен, тъй като можем да видим, че 601 612 реда са заредени в рамките на няколко минути.
Неща за запомняне
- Power Query не променя оригиналните данни. Вместо да променя оригиналните източници, той записва всяка стъпка, която предприема потребителят, докато свързва или трансформира данните, и след като потребителят завърши оформянето на данните, той взема прецизирания набор от данни и го внася в работната книга.
- Power Query чувствителен е към регистъра.
- Докато консолидираме файловете в посочената папка, трябва да се уверим, че използвайки колоната „Разширение“ и трябва да изключим временните файлове (с разширението „.tmp“ и името на тези файлове започва със знака „~“) като Power Query може да импортира и тези файлове.
