Power Query е вграден във версиите на Windows на Office 365, Excel 2016, Excel 2019 и се предлага като безплатно изтегляне във версиите на Windows на Excel 2010 и Excel 2013. Инструментът е предназначен за извличане, трансформиране и зареждане на данни в Excel от разнообразие от източници. Най-добрата част: Power Query помни стъпките ви и ще ги възпроизведе, когато искате да опресните данните. Това означава, че можете да почистите данните в Ден 1 за 80% от нормалното време и можете да почистите данните в Дни от 2 до 400, като просто щракнете върху Обновяване.
Казвам това за много нови функции на Excel, но това наистина е най-добрата функция, която може да се появи в Excel от 20 години.
Разказвам история в семинарите си на живо за това как Power Query е измислена като патерица за клиентите на SQL Server Analysis Services, които са били принудени да използват Excel, за да имат достъп до Power Pivot. Но Power Query продължаваше да се подобрява и всеки човек, който използва Excel, трябва да отделя време, за да научи Power Query.
Вземете Power Query
Може вече да имате Power Query. Той е в групата Get & Transform в раздела Data.
Но ако сте в Excel 2010 или Excel 2013, отидете в Интернет и потърсете Изтегляне на Power Query. Командите ви за Power Query ще се появят в специален раздел Power Query в лентата.
Почистете данните за първи път в Power Query
За да ви дадем пример за страхотността на Power Query, кажете, че всеки ден получавате показания файл. Колона А не е попълнена. Четвърти преминават през, вместо надолу по страницата.
За да започнете, запазете тази работна книга на вашия твърд диск. Поставете го на предсказуемо място с име, което ще използвате за този файл всеки ден.
В Excel изберете Получаване на данни, От файл, От работна книга.
Прегледайте работната книга. В прозореца за предварителен преглед кликнете върху Sheet1. Вместо да щракнете върху Зареждане, щракнете върху Редактиране. Сега виждате работната книга в малко по-различна мрежа - решетката Power Power.
Сега трябва да коригирате всички празни клетки в колона А. Ако трябваше да направите това в потребителския интерфейс на Excel, тромавата последователност от команди е Начало, Намиране и Избиране, Отидете на Специални, Празни, Равни, Стрелка нагоре, Ctrl + Enter .
В Power Query изберете Transform, Fill, Down.
Всички нулеви стойности се заменят със стойността отгоре. С Power Query отнема три кликвания вместо седем.
Следващ проблем: Кварталите преминават през, вместо надолу. В Excel можете да поправите това с обобщена таблица на няколко диапазона на консолидация. Това изисква 12 стъпки и 23+ кликвания.
В Power Query изберете двете колони, които не са четвърти. Отворете падащото меню Unpivot Columns в раздела Transform и изберете Unpivot Other Columns, както е показано по-долу.
Щракнете с десния бутон на мишката върху новосъздадената колона Attribute и я преименувайте Quarter вместо Attribute. Двадесет и повече кликвания в Excel стават пет щраквания в Power Query.
Сега, за да бъдем честни, не всяка стъпка на почистване е по-кратка в Power Query, отколкото в Excel. Премахването на колона все още означава да щракнете с десния бутон върху колона и да изберете Премахване на колона. Но за да бъда честен, историята тук не е за спестяване на време в Ден 1.
Изчакайте: Power Query помни всичките ви стъпки
Погледнете от дясната страна на прозореца на Power Query. Има списък, наречен Приложени стъпки. Това е незабавна одиторска пътека на всички ваши стъпки. Щракнете върху иконата на зъбно колело, за да промените избора си в тази стъпка и да промените каскадно през следващите стъпки. Кликнете върху която и да е стъпка, за да видите как са изглеждали данните преди тази стъпка.
Когато приключите с почистването на данните, щракнете върху Затвори и зареди, както е показано по-долу.
Бакшиш
Ако данните ви са повече от 1 048 576 реда, можете да използвате падащото меню Затваряне и зареждане, за да заредите данните директно в Power Pivot Data Model, който може да побере 995 милиона реда, ако имате достатъчно памет, инсталирана на машината.
След няколко секунди трансформираните ви данни се появяват в Excel. Страхотно.
Изплащането: Почистете данните утре с едно кликване
Но отново, историята на Power Query не е свързана с спестяване на време в Ден 1. Когато изберете данните, върнати от Power Query, панел Queries & Connections се появява в дясната страна на Excel и върху него има бутон Refresh. (Тук се нуждаем от бутон Редактиране, но тъй като няма такъв, трябва да щракнете с десния бутон на мишката върху оригиналната заявка, за да видите или да направите промени в оригиналната заявка).
Забавно е да почиствам данните на Ден 1. Обичам да правя нещо ново. Но когато моят мениджър види резултата от доклада и казва „Красиво. Можете ли да правите това всеки ден? ” Бързо растя да мразя умората от почистването на един и същ набор от данни всеки ден.
И така, за да демонстрирам Ден 400 на почистване на данните, аз напълно промених оригиналния файл. Нови продукти, нови клиенти, по-малък брой, повече редове, както е показано по-долу. Запазвам тази нова версия на файла в същия път и със същото име на файла като оригиналния файл.
Ако отворя работната книга на заявката и щракна върху Refresh, след няколко секунди Power Query отчита 92 реда вместо 68 реда.
Почистването на данните на Ден 2, Ден 3, Ден, 4, … Ден 400, … Ден Infinity сега отнема две щраквания.
Този единствен пример само надрасква повърхността на Power Query. Ако прекарате два часа с книгата, M е за (Data) Monkey от Кен Пулс и Мигел Ескобар, ще научите за други функции, като тези:
- Комбиниране на всички Excel или CSV файлове от папка в една мрежа на Excel
- Преобразуване на клетка с Apple; Банан; Череша; Копър; Патладжан до пет реда в Excel
- Извършване на VLOOKUP за справочна работна книга, докато въвеждате данни в Power Query
- Извършване на една заявка във функция, която може да се приложи към всеки ред в Excel
За пълно описание на Power Query вижте M Is for (Data) Monkey от Кен Пулс и Мигел Ескобар. До края на 2019 г. ще бъде налице второто издание „Master Your Data“.
Благодаря на Мигел Ескобар, Роб Гарсия, Майк Гирвин, Рей Хаузър и Колин Майкъл за номинирането на Power Query.
