Излизало ли ви е съобщение за грешка в Power Query при смяна на имената на колони в данните? Точно за такъв проблем ме помолиха да помогна онзи ден. Причината е логична, решението – лесно, но не толкова очевидно за хора, които работят в Power Query само с бутоните от лентата. Ето защо реших да напиша тази статия, в която да обясня проблема, защо се случва и как се поправя. По-лесно е отколкото предполагате, отделете 5 минути за четене, ще имате несъразмерно по-голяма полза.
Какъв е проблемът
Имаме таблица с имена на отбори, от кой град са, как се казва стадионът им и какви са клубните цветове. Направена е заявка в Power Query, която взима таблицата, прави малко обработки, филтрира всички отбори, които имат жълт цвят и връща вдясно таблица с името на отбора и стадиона.
При създаване на заявката последната колона се е казвала „Цветове“, но впоследствие е променена на „Клубни цветове“ и сега при рефреш получаваме следното съобщение за грешка в Power Query.
Каква е причината
Ако погледнете внимателно картинката по-горе – причината е съвсем ясна. Power Query търси колона с името „Цветове“, но не я намира и не знае какво да прави. Затова спира и извежда съобщение за грешка. Това е така, защото Power Query не се ориентира по позицията на колоната в таблицата или по съдържанието ѝ, а запаметява буквално името ѝ в М кода. За него „Цветове“ и „Клубни Цветове“ са напълно различни обекти.
Това поведение е напълно логично и очаквано. Да, всеки човек ще се досети, че „Клубни Цветове“ е същото като „Цветове“, само че „Клубни“ (особено Валери Божинов) и ще се ориентира в контекста на ситуацията. Но машината не работи така. Тя прави точно каквото ѝ е казано и ако нещо е различно – извежда съобщение за грешка.
Нека да надникнем „под капака“ на Power Query – Data > Queries & Connections
Query-то се появява вдясно
Щракаме два пъти с левия бутон на мишката, за да влезнем в Power Query. Алтернативата е да стъпим на зелената таблица с резултата от заявката и да отидем на Data > Query > Edit
Без значение по кой начин резултатът е, че ни се отваря менюто на Power Query. Ако за пръв път влизате тук или досега сте работили само с менютата от лентата с бутони, то твърде вероятно е да виждате нещо такова.
За да можем да решим проблема на нас ни е необходимо да виждаме и лентата с формули (Formula Bar). Активираме я като отиваме в менюто View и слагаме отметка на Formula Bar.
Така се появява лента за формули много подобна на тази в Excel.
Всъщност всеки път, когато кликаме нещо по бутоните на Power Query това създава формула на езика М, която се изписва в тази лента. Ще забележите, че в М кода имената на колоните са оградени с кавички и са оцветени в керемидено червено – това са фиксирани текстови стойности, които Power Query използва буквално. Именно заради тях, ако името се промени в източника, заявката „се счупва“.
Как намираме къде е грешката
Вероятно вече знаете, а ако не знаете, сега ще узнаете, че всяко действие, което правим в Power Query се записва като отделна стъпка и стъпките се показват вдясно в секцията Applied Steps. За да отстраним грешката първо трябва да разберем на коя точно стъпка тя се случва.
Power Query много услужливо ни предлага да отидем направо там, където е грешката като натиснем бутона Go to error.
Това ни закарва в стъпката, озаглавена „Reordered Columns“, и виждаме в лентата за бутони, че Power Query търси колоната „Цветове“, каквато всъщност вече няма в таблицата.
Как поправяме грешката
Изключително лесно е. Достатъчно е да влезнем в лентата за формули и да променим „Цветове“ на „Клубни Цветове“
На секундата се случва чудо – проблемът е решен
НО
На следващата стъпка отново имаме грешка
Защо? Защото и там Power Query е записал във формулата „Цветове“ при първоначалното създаване на заявката.
Досещате се предполагам – и тук трябва да променим „Цветове“ с „Клубни Цветове“ и това ще реши проблема. За тази стъпка. Ако го има на следващата – отново и отново повтаряме същото действие докато изчистим всички грешки.
Как да поправим грешката в много стъпки едновременно
Ако имате 2-3 стъки, както в това просто Query, удачно е да минете набързо през тях и да поправите ръчно имената на колоните. Но ако става въпрос за много стъпки и повече от една колона, тогава е удачно да се подходи по малко по-автоматизиран начин.
В Power Query отиваме на Home -> Advanced Editor
Това отваря менюто с М-кода на заявката
Копираме го целия и го пействаме в Notepad, Word или друг текстов редактор. Там подменяме (с Find&Replace -> Replace All) „Цветове“ с „Клубни Цветове“. След това копираме отново целия код и го пействаме обратно в Advanced Editor-а на Power Query. Цъкаме ОК и готово
Вместо заключение
В тази статия разгледахме един типичен случай, когато получаваме грешка в Power Query при смяна на имената на колони в данните. Видяхме, че за решаването на проблема потребителското меню на Power Query не е достатъчно. Това показва нагледно ограниченията на работата в Power Query само с менюта и загатва как може да увеличите продуктивността и възможностите си дори с малко знание за езика М и неговите функции. Ако искате да задълбочите познанията си по езика М, горещо препоръчвам книгата на Чандийп Чабра „Power Query Beyond The User Interface„. Може да следите, разбира се, и статиите, които публикувам в специалната секция на блога за Power Query.
Надявам се, че тази статия ви е била полезна. Ако искате да повишите ефективността си в Excel като цяло, може да прочетете книгата ми „Ексел До“. В нея споделям една изпитана в практиката ми система за работа в Ексел. Книгата е достъпна напълно безплатно тук.