Power Query е чудесен инструмент за подреждане на неструктурирани данни във вид, удобен за анализ. В тази статия ще ви покажа как може да го използвате за решаването на една специфична задача.
Какъв е казусът?
Представете си, че HR отделът на една фирма има определен бюджет за допълнителни придобивки на служителите – курсове, тренировки, масажи и т.н. За да следят реалния разход и да анализират използването на тези придобивки, те получават справка в следния вид:
Тя съдържа името на съответния служител (оцветено в синьо), какви придобивки е ползвал (Service), колко пъти (Number), колко минути е била общата продължителност (Time (minutes)) и колко е струвало това на фирмата (Amount BGN).
Информацията, въпреки че е подредена добре, всъщност е неструктурирана от гледна точка анализа на данни. За да можем да анализираме тези данни, първо трябва да ги преобразуваме до следния вид:
Тази скучна на вид таблица всъщност ни дава възможност да правим всякакви обобщения, разбивки и сечения на данните. От нея с помощта на пивот таблица или формули можем да видим кои са най-често използваните придобивки или да направим справка на разходите по видове дейности. А по името на служителя можем да свържем тези данни със списъка на служителите по отдели за още по-детайлни анализи. И например, ако служителите от Финансовия отдел са преминали общо 120 часа обучение по Excel на стойност 5000 лв., а отчетите им закъсняват редовно и са пълни с грешки, да се запитаме – дали това обучение е качествено и пълноценно.
Пътят от първата (неструктурирана) таблица до втората (нормализирана) преминава през множество предизвикателства:
- Създаване на колона с името на всеки служител срещу ползваните от него придобивки
- Всеки служител е ползвал различен брой придобивки. Например Пешо е ползвал четири, а Гошо – шест
- Но най-интересното предизвикателство е, че имаме по две таблици с ползвани придобивки за всеки служител, които са една до друга
В следващите редове ще ви покажа едно възможно решение именно на последния проблем.
Подреждане на таблиците една под друга
Както с повечето неща в Ексел и за този проблем има повече от едно решение, но на мен най-ефективен ми изглежда този:
1. Зареждане данните в Power Query
2. Обединяване на първите 4 колони в една.
Маркираме ги и отиваме на Transform > Merge Columns. Целта на това действие е да обединим тези четири колони в една
В менюто, което се появява трябва да зададем разделител между данните от четирите колони (Separator), а под него – име на новата колона. По подразбиране Power Query задава име на колоната Merged и ние не сме длъжни да го променяме.
Кликаме на стрелката в полето за разделител и избираме Custom
Появява се ново поле, в което трябва да зададем какъв да бъде разделителят. Може да бъде всичко – точка, запетая, числа, букви, символи, дори цели думи и словосъчетания. Номерът тук е да изберем такъв разделител, който не съществува в текста. Това е необходимо, защото след няколко стъпки ще използваме този разделител да разделим колоните отново и не искаме да повредим данните. Обикновено е крайно невероятно един текст да съдържа този символ – | и затова съм използвал него. За всеки случай предварително може да го проверим с помощта на Find & Replace инструмента в Ексел.
Когато дадем ОК, на мястото на четирите колони се появява една нова, която съдържа цялата информация, разделена с вертикални черти.
3. Обединяване на вторите 4 колони в една
Просто повтаряме горната стъпка, като ползваме същия разделител и крайният резултат трябва да изглежда така
4. Маркираме двете нови колони и …. ги обединяваме
Това може и да ви се стори нелогично, но смисълът ще се изясни след малко. Важна подробност е, че този път използваме някакъв друг разделител, за да сработи номерът. Аз в случая съм използвал „колибката“ – ^
А резултатът е тази колона
5. Подреждане едно под друго
Сега внимавайте, защото точно тук се случва магията. Както сме избрали колоната отиваме на Transform > Split Column > By Delimiter
След това избираме:
- Custom Delimiter и задаваме колибката
- Each occurrence of the delimiter, т.е. да раздели навсякъде, където срещне “^” за разделител. Макар че на точно тази стъпка това да не е от значение
- Накрая в Advanced options избираме Rows. С това указваме на Power Query, че искаме данните от втората колона да ги разположи под редовете на първата
Вижте какво се получава:
6. Финално подреждане
Сега отново трябва да разделим колоната, като разликите с предната стъпка са две: за разделител ползваме вертикалната черта, а в Advanced избираме резултатът да е подреден по колони (Columns)
Крайният резултат е точно това, което искаме – всички ползвани придобивки са подредени в една таблица с четири колони.
От тук нататък, разбира се, има още много работа докато го докараме до крайната таблица, която ни е необходима. Но най-тежката работа сме я свършили, което означава, че спокойно можем да се почерпим с нещо вкусно.