Добавянето на колони под условие (conditional column) е често използвана и много полезна техника при обработката на данни. В тази статия ще ви покажа как да я прилагате и отново за пример ще ни послужи таблицата с пътуванията на български граждани в чужбина, публикувани от НСИ.
Нека най-напред да си припомним как изглеждат тези данни в оригиналния им вид:
Виждате, че в таблицата има два вида редове. Първите са с обикновен шрифт и показват данните за отделните страни – Австрия, Белгия, Германия и т.н. Вторите са болдирани и представляват сбор на някаква група страни. На картинката по-горе са оградени в червено „Европейски съюз“ и „Други европейски страни“. А най-отгоре стои и редът „ОБЩО“.
т.нар. тотални редове присъстват и в нашата трансформирана с Power Query таблица:
По принцип, ако разглеждаме данните на НСИ като готов отчет, тези редове са полезни и дават възможност да вникнем бързо в „голямата картина“. Но ако искаме да направим подробен и задълбочен анализ на данните, те не са необходими – винаги можем чрез пивот таблица или формули да си ги създадем щом имаме суровите данни. А понякога такива редове могат и да ни пречат, например, ако искаме да направим графика по страни. Затова добрата практика е тези редове да се премахнат от таблицата и да оставим само най-детайлните данни (т.е. на ниво отделна страна).
Премахването е лесно с прилагане на филтър в Power Query. Но така ще загубим ценна информация, а именно – коя страна към коя група принадлежи. По-добре е да направим така, че в отделна колона срещу всяка страна да пише към коя група спада – Европейски съюз, Други европейски страни, Други страни и т.н.
И ето тук идва ред на добавянето на колона под условие (conditional column). Отиваме на Add Column -> Conditional Column
Появява се менюто Add Conditional Column. На пръв поглед то е доста стряскащо, но всъщност е много интуитивно и ни води само към желания резултат.
Най-отгоре е полето New Column Name, буквално преведено – Име на новата колона. По подразбиране Power Query задава стойност Custom, която ние можем (и е желателно) да променим. В случая аз съм избрал новата колона да се казва „Група страни“
На следващия ред, този на зеления фон, изграждаме нашето условие (IF statement). От ляво на дясно какво се случва:
If – ако
Column name – задаваме колоната, която ще бъде оценявана. Става много лесно – като натиснем стрелкичката в десния край на полето, се появава списък с всички колони в таблицата. От него избираме тази, която ни интересува, в случая „Страни“
Operator – това е именно условието, по което оценяваме колоната. Отново с натискане на стрелката вдясно се появява списък с възможни стойности. За опитните потребители на Ексел той не изглежда непознат. Същият е като при филтрирането. Избираме „equals“, т.е. „равно на“
Value – тук казваме на какво трябва да е равна стойността в колона „Страни“. Имаме падащо меню с няколко възможности, което се появява като натиснем бутона отляво на полето:
Първата възможност е Enter a value, т.е. да въведем „твърда“ стойност. Просто избираме тази опция и записваме „Европейски съюз“ в полето.
Втората възможност е Select a column. Ако изберем нея, то Power Query ще гледа дали стойността в колона „Страни“ е равна на стойността на същия ред в друга колона от таблицата.
Третата опция Parameter за момента не е активна. Ще дойде статия в един хубав момент, ще стигнем и до параметрите.
Последно в този ред е полето Output – тук задаваме стойността, която да върне Power Query, ако е изпълнено условието. Отново имаме бутон за опции отляво. Бихме могли да изберем “Enter a value“ и да въведем „твърда“ стойност. В случая подходящи биха били „Европейски съюз“, „ЕС“, “EU”. Но по-гъвкавият вариант е да изберем “Select a column” и след това – колоната „Страни“.
Така стигаме до последния ред в менюто Add Conditional Column. Това е Else, т.е. каква стойност да ни върне Power Query, ако условието не е спазено. И тук имаме три избора – твърда стойност, стойността от някоя колона в таблицата или параметър. Но аз ще го оставя празно, защото така ми е необходимо.
Нека да спрем за момент и да видим какво искаме да направи Power Query:
Да създаде нова колона „Група страни“, в която ако стойността в колона „Страни“ е равна на „Европейски съюз“ да върне тази стойност, в противен случай да остави празно. И когато кликнем ОК, резултатът е точно това:
За да дефинираме друга група трябва да се върнем в менюто за колоната под условие като кликнем два пъти върху последната стъпка (Added Conditional Column).
И след това добавяме съответните групи по описания по-горе начин
Резултатът от тази стъпка е нова колона с име „Група страни“.
В нея имаме стойностите на тоталните редове, а между тях – празни клетки (null). С това сме само на няколко клика от крайната ни цел. Трябва само цъкнем с десния бутон върху заглавието на колона „Група страни“ и от появилото се меню да изберем Fill -> Down и ….
… О чудо! колоната е запълнена със съответните стойности!
Натискаме бутона за филтър на колоната, за да се уверим, че съдържа всички необходими стойности, а и за да махнем излишните (null)
Нашата таблица е вече готова и с Close&Load можем да я заредим в Ексел
Това е крайният резултат: