Добавяне на колона под условие (add conditional column) в Power Query

Добавянето на колони под условие (conditional column) е често използвана и много полезна техника при обработката на данни. В тази статия ще ви покажа как да я прилагате и отново за пример ще ни послужи таблицата с пътуванията на български граждани в чужбина, публикувани от НСИ.

Нека най-напред да си припомним как изглеждат тези данни в оригиналния им вид:

Power Query conditional column

Виждате, че в таблицата има два вида редове. Първите са с обикновен шрифт и показват данните за отделните страни – Австрия, Белгия, Германия и т.н. Вторите са болдирани и представляват сбор на някаква група страни. На картинката по-горе са оградени в червено „Европейски съюз“ и „Други европейски страни“. А най-отгоре стои и редът „ОБЩО“.

т.нар. тотални редове присъстват и в нашата трансформирана с Power Query таблица:

Power Query conditional column

По принцип, ако разглеждаме данните на НСИ като готов отчет, тези редове са полезни и дават възможност да вникнем бързо в „голямата картина“. Но ако искаме да направим подробен и задълбочен анализ на данните, те не са необходими – винаги можем чрез пивот таблица или формули да си ги създадем щом имаме суровите данни. А понякога такива редове могат и да ни пречат, например, ако искаме да направим графика по страни. Затова добрата практика е тези редове да се премахнат от таблицата и да оставим само най-детайлните данни (т.е. на ниво отделна страна).

Премахването е лесно с прилагане на филтър в Power Query. Но така ще загубим ценна информация, а именно – коя страна към коя група принадлежи. По-добре е да направим така, че в отделна колона срещу всяка страна да пише към коя група спада – Европейски съюз, Други европейски страни, Други страни и т.н.

И ето тук идва ред на добавянето на колона под условие (conditional column). Отиваме на Add Column -> Conditional Column

Power Query conditional column

Появява се менюто Add Conditional Column. На пръв поглед то е доста стряскащо, но всъщност е много интуитивно и ни води само към желания резултат.

Power Query conditional column

Най-отгоре е полето New Column Name, буквално преведено – Име на новата колона. По подразбиране Power Query задава стойност Custom, която ние можем (и е желателно) да променим. В случая аз съм избрал новата колона да се казва „Група страни“

Power Query conditional column

На следващия ред, този на зеления фон, изграждаме нашето условие (IF statement). От ляво на дясно какво се случва:

If – ако

Column name – задаваме колоната, която ще бъде оценявана. Става много лесно – като натиснем стрелкичката в десния край на полето, се появава списък с всички колони в таблицата. От него избираме тази, която ни интересува, в случая „Страни“

Power Query conditional column

Operator – това е именно условието, по което оценяваме колоната. Отново с натискане на стрелката вдясно се появява списък с възможни стойности. За опитните потребители на Ексел той не изглежда непознат. Същият е като при филтрирането. Избираме „equals“, т.е. „равно на“

Power Query conditional column

Value – тук казваме на какво трябва да е равна стойността в колона „Страни“. Имаме падащо меню с няколко възможности, което се появява като натиснем бутона отляво на полето:

Power Query conditional column

Първата възможност е Enter a value, т.е. да въведем „твърда“ стойност. Просто избираме тази опция и записваме „Европейски съюз“ в полето.

Втората възможност е Select a column. Ако изберем нея, то Power Query ще гледа дали стойността в колона „Страни“ е равна на стойността на същия ред в друга колона от таблицата.

Третата опция Parameter за момента не е активна. Ще дойде статия в един хубав момент, ще стигнем и до параметрите.

Последно в този ред е полето Output – тук задаваме стойността, която да върне Power Query, ако е изпълнено условието. Отново имаме бутон за опции отляво. Бихме могли да изберем “Enter a value“ и да въведем „твърда“ стойност. В случая подходящи биха били „Европейски съюз“, „ЕС“, “EU”. Но по-гъвкавият вариант е да изберем “Select a column” и след това – колоната „Страни“.

Power Query conditional column

Така стигаме до последния ред в менюто Add Conditional Column. Това е Else, т.е. каква стойност да ни върне Power Query, ако условието не е спазено. И тук имаме три избора – твърда стойност, стойността от някоя колона в таблицата или параметър. Но аз ще го оставя празно, защото така ми е необходимо.

Power Query conditional column

Нека да спрем за момент и да видим какво искаме да направи Power Query:

Power Query conditional column

Да създаде нова колона „Група страни“, в която ако стойността в колона „Страни“ е равна на „Европейски съюз“ да върне тази стойност, в противен случай да остави празно. И когато кликнем ОК, резултатът е точно това:

Power Query conditional column

За да дефинираме друга група трябва да се върнем в менюто за колоната под условие като кликнем два пъти върху последната стъпка (Added Conditional Column).

Power Query conditional column

И след това добавяме съответните групи по описания по-горе начин

Power Query conditional column

Резултатът от тази стъпка е нова колона с име „Група страни“.

Power Query conditional column

В нея имаме стойностите на тоталните редове, а между тях – празни клетки (null). С това сме само на няколко клика от крайната ни цел. Трябва само цъкнем с десния бутон върху заглавието на колона „Група страни“ и от появилото се меню да изберем Fill -> Down и ….

Power Query conditional column

… О чудо! колоната е запълнена със съответните стойности!

Power Query conditional column

Натискаме бутона за филтър на колоната, за да се уверим, че съдържа всички необходими стойности, а и за да махнем излишните (null)

Power Query conditional column

Нашата таблица е вече готова и с Close&Load можем да я заредим в Ексел

Power Query conditional column

Това е крайният резултат:

Power Query conditional column

 

Публикувано в 5 минути Power Query с етикети , , . Постоянна връзка.