Remove Errors в Power Query е една от многото дребни глезотии, с които този инструмент улеснява живота на всеки, който чисти и подрежда данни с него. Наскоро, докато работех по един отчет, забелязах една особеност в работата на тази функционалност, на която искам да обърна внимание в тази статия.
Казусът
Да кажем, че обработваме банково извлечение за кредитни карти. На картинката може да видите един типичен такъв файл, зареден в Power Query (използван е форматът на реална банка в България, но данните са абсолютно измислени).
Файлът съдържа доста полезна информация, но очевидно е във вид, който се нуждае от почистване и подреждане. Тук ще се съсредоточим само върху колоната „Дата“. Виждаме, че освен датите на операциите с картата, в нея има празни редове (null) и клетки с текст. За да приведем тази колона във вид, удобен за анализ, трябва да премахнем празните клетки и текста и да оставим само датите
Стандартното решение
Обичайно решението е да филтрираме това, което е излишно. За целта кликаме на стрелката за филтър вдясно от името на колоната и после махаме отметката от ненужните данни.
Резултатът е точно това, което очакваме:
Но има няколко проблема:
1. Неудобно е при много стойности
Представете си, че файлът съдържа записи за, примерно, 50 карти. Това не е необичайно за компания с повече от 100 служителя, каквито има много в България. Тогава трябва ръчно да кликаме 50 пъти, което е неудобно и предпоставка за грешки.
2. Не е гъвкаво
За незапознатите с тънкостите на Power Query – всяко действие, което правим с цъкане по менютата, всъщност създава формула, написана на езика М. Формулата се изписва в полето за формули (formula bar). Може да видите как изглежда формулата за филтрирането, което направихме по-горе
Няма да навлизам в подробности, за да не обърквам читателя. Ще кажа само, че всичко, което виждате изписано с червено във формулата е фиксирана стойност. В тази формула ние буквално казваме на Power Query заедно с празните клетки да премахне тези три номера на карти и думата „Общо“. Нищо друго.
Представете си, че следващия път получим файл, в който тези номера не присъстват. Power Query няма да филтрира нищо освен празните клетки. Той просто търси фиксираните във формулата стойности.
По-доброто решение: Remove errors в Power Query
Ето как можем да подходим хитро, за да подсигурим гъвкаво и лесно за прилагане решение:
1. Променяме типа на данните в колона „Дата“
Това става като цъкнем на квадратчето вляво от името на колоната и след това избираме формат Date (Дата)
Това води до (на пръв поглед) неочакван резултат – празните си остават празни, датите вече са истински дати (подравнени вдясно), но там където имаше текст сега има грешка – Error.
Обяснението е, че когато зададем формат на колона Power Query, програмата се опитва да превърне всички данни в този формат. Това, което прилича на дата – Power Query го превръща в дата. Празните стойности (null) са неутрални и си остават такива. Но текстът не може да бъде превърнат в дата и Power Query ни казва – „Имаш грешка, тази стойност не може да е дата“.
Обърнете внимание на една подробност – независимо, че стойността на ред 10 е Errorr в колоната за дата, в другите тя си остава null.
2. Филтрираме ненужното
Може би сега очаквате, че в колоната „Дата“ имаме дати, празни клетки и клетки, в които пише Error. Така ние просто трябва да махнем отметката пред null и Error и сме готови. Това е едновременно удобно (само две стойности филтрираме) и гъвкаво – винаги ще имаме само тези две.
Да, ама не.
Дати има, празно има, но няма Error.
Това е така, защото „Error“ не е дума, която се изписва в клетките с грешки. „Error“ е специален вид данни – просто грешка. Power Query просто отказва да работи с този тип данни.
Така, когато махнем отметката пред null получаваме това
Под редовете с данни има една грешка. Но и тази болка си има лек.
3. Премахваме грешките с Remove errors
Тук можем да се възползваме от една от многото благини, които предлага Power Query – Remove Errors. Просто кликаме с десния бутон на мишката върху името на колоната и от менюто избираме Remove Errors. Така точно с два клика премахваме грешките от колоната.
Или поне такова беше моето очакване. Защото когато го направих резултатът беше … липса на резултат. Редът с грешката продължава да ни се усмихва най-нахално.
Когато ми се случи за пръв път, беше доста озадачаващо. Най-малкото бях го ползвал и преди и знаех, че работи. Трябваше ми известно четене и разговор с Claude, за да разбера какво точно не работи.
Ключът е в реда с грешката на последната картинка. Обърнете внимание, че там Error пише не само на колоната „Дата“, но и на всички колони. Освен това е само един ред, а не няколко. Очевидно се случва следното:
-> При смяна на формата в колона „Дата“ се появява Error там, където има текст. И това е грешка в клетката – тя съдържа стойност, несъвместима с типа на данните в колоната.
-> Когато филтрираме, за да премахнем null стойностите, Power Query обединява всички грешки в един грешен запис – последния ред. Там във всяка колона пише Error, което означава, че целият ред е грешен. Но това е различен тип грешка, не е грешка при форматиране, а просто грешен запис.
-> Очевидно Remove Errors в Power Query премахва само грешките от първия тип – тези, които са резултат от невъзможна операция. Но не премахва грешен запис.
-> Нещо по-лошо: Power Query спира до грешния запис и не зарежда данните след него
4. Правилната употреба на Remove errors в Power Query
Решението на проблема е съвсем лесно. Разбрахме, че Remove errors в Power Query премахва грешки, които са резултат от някакво действие. Тогава трябва да го прилагаме непосредствено след операцията, която води до грешките. В нашия случай – първо премахваме грешките, а едва след това филтрираме. Така премахваме възможността последващи операции да превърнат грешката в грешен запис.
Внимание! Не е нужно да изтривате стъпките – просто с влачене преместете стъпката „Removed Errors” веднага след операцията, при която са се появили грешките (в случая – “Changed Type”).
Ето нагледно разликата в употребата на Remove Errors
Заключение
В тази статия видяхме ползата от Remove errors в Power Query и как е правилно да се използва тази функционалност. За да избегнете главоболия – просто го прилагайте непосредствено след стъпката, в която се появяват грешките.
За още статии, свързани с Power Query – вижте тук.