В тази статия ще разгледам набързо възможностите за филтриране в Power Query (PowerQuery Filter). Прилагането на филтър е една от най-често срещаните операции в Ексел и съответно добре позната на хората, които редовно работят с програмата. Но също както с Text-to-Columns Power Query предлага повече гъвкавост и функционалност.
Да разгледаме един съвсем простичък пример. Имаме списък с ученици от различни училища и техните резултати от някакво състезание по математика. No е уникален номер, School е училището, от което е ученикът, от 1 до 6 са точките, които е получил ученикът за съответните задачи и накрая имаме общ сбор точки, т.е. Total.
Целта ни е да извадим списък с ученици, които:
• Учат в математическа гимназия (СМГ или НПМГ)
• Имат по-малко от 45 точки на задача 1
• Имат повече от 7 точки на задача 6
Както се казва – това и баба го знае. Първо ще сложим филтър в колоната School по този начин.
След това ще филтрираме в колона 1 така:
И накрая в колона 6 така:
Ето го и резултатът:
Лично аз обаче намирам за малко изнервящо това непрекъснато цъкане колона след колона. И дълго време се чудех няма ли някакъв начин да си сложа филтрите наведнъж. С PowerQuery Filter има, затова нека заредим данните и да видим как става:
Филтрирането можем да го направим по същия начин като в Ексел – колона след колона.
Но по-добре в колоната School да дадем Text Filters -> Equals…
Излиза прозорец, който визуално много наподобява този при филтрирането в Ексел
Бързо въвеждам филтрите за училища – НПМГ или (Or) СМГ
Но следващото нещо, което правя е не да кликна ОК, а бутона Advanced
Разликата е почти незабележима, но съществена – появява се един нов бутон Add Clause. А когато натиснем върху него – появява се нов ред за условия
Така бързо въвеждам условията за задача 1 (<45) и 6 (>7).
След това давам ОК и ….
Греда!!!
Резултатите не са това, което очаквам – има поне няколко реда, на които точките за задача 1 са повече от 45.
Но спокойно! В Power Query имаме възможност да коригираме каквото сме сътворили на всяка стъпка. Достатъчно е в Applied Steps да кликнем два пъти върху съответната стъпка – в случая Filtered Rows.
Грешката е, че имаме две училища, но само за едно от тях сме задали условията за точките. Затова бързо добавям два нови реда, които са същите като горните.
Следващата стъпка е да цъкна върху трите точки най-вдясно на реда за всяка клауза. Имайте предвид, че те се появяват само когато клаузата е маркирана (тогава тя се оцветява в зелено).
Когато кликнем върху трите точки, появява се меню, с което можем да преместим всеки ред нагоре, надолу или да го изтрием. За съжаление не става с влачене или Cut/Paste. Може би нещо, по което програмистите в Микрософт трябва да помислят.
Ето тук съм ги наредил както си трябва и остава само да натисна ОК
А резултатът, както виждате, е точно според очакванията:
Отгоре в зелено е оригиналната таблица, филтрирана в Ексел. Отдолу в оранжево – резултатът от заявката с прилагането на PowerQuery Filter.
До тук PowerQuery Filter не изглежда чак толкова впечатляващо в сравнение с обикновения Ексел. Но нека ви запитам нещо:
Имаме таблица с нови записи (в червено са оцветени редовете, които отговарят на критериите)
Ако трябва да добавите тези нови редове към таблицата с данните и след това да филтрирате отново по същите критерии, какво ще направите?
Нека да позная.
- Ще махнете филтрите от всички колони
- След това ще пейстнете новите данни най-отдолу на таблицата
- И най-накрая ще приложите ОТНОВО същата процедура по филтриране на три колони.
Познах, нали? Ами и аз съм го играл това хоро много дълго време.
А ето какво ще се случи в Power Query:
- Няма да разфилтрирам таблицата с данните, защото не съм я филтрирал преди това. Тя си стои непокътната и всички действия се осъществяват в Power Query, крайният резултат е нова (резултатна) таблица
- Пействам новите данни под старите
- Отивам в резултатната таблица, десен бутон и избирам Refresh
И готово – двата нови записа, са добавени с един клик!
Това е най-голямата полза от Power Query. Той автоматизира повтаряеми действия. Просто с PowerQuery Filter потребителят прави ЕДНОКРАТНО абсолютно същите неща, които би направил и в Ексел. Може би с едно-две допълнения, за които така или иначе отдавна сме си мечтали. От там нататък – само добавяме новите данни и рефрешваме.
Оставям на вас да си пресметнете колко време ще ви спести Power Query с неговия филтър.
Файлът с данните можете да изтеглите от тук
А ако искате да упражните работа с PowerQuery Filter, предлагам ви да добавите към критериите и 125СУ.