В тази статия ще демонстрирам 7 техники в Power Query за обработка на текст. Използвам ги редовно при различни случаи, но за пръв път срещам казус, който се решава със съчетаването им. Статията е малко дълга и е подходяща за по-напреднали потребители на Power Query, които имат интерес и опит в обработка на текстова информация.
Какъв е казусът?
Имаме текстови файл със списък от въпроси (Questions) и отговори (Answers). Те обаче са в доста неподреден и неудобен за използване вид:
Целта е да се преобразува така, че всичко между [QUESTION]: и [ANSWER]: да е в една колона (Questions), а всичко между [ANSWER]: и –––––––––––––- да е в друга колона (Answers). Ето така:
Както виждате изходната информация има добре изразена структура, но сме изправени пред доста предизвикателства:
- Както въпросите, така и отговорите не винаги са на един ред
- Текстът на всеки ред е с различна дължина
- Между въпросите и отговорите има различен брой празни редове
- Част от редовете не са ни необходими – тези, в които са празни или съдържат [QUESTION], [ANSWER] и черти
- Въпросите и отговорите са един под друг, а ние ги искаме в съседни колони
Все пак имаме за какво да се захванем и това са ключовите думи [QUESTION]: и [ANSWER]:. Всъщност, ако използваме терминологията на програмистите, това са стрингове.
Импорт в Power Query
Първата стъпка е да отворим нов ексел файл и от Data > Get & Transform data > From Text/Csv да стартираме Power Query
Намираме папката с файла и го избираме
Тук натискаме Transform Data
И нашият файл се зарежда в Power Query, но по малко необичаен начин – като таблица с много редове (което е ОК) но и много колони. А това вече не е ОК.
Идва ред за първата техника, която научих по трудния начин – с проба/грешка и много ровене в гугъл. Трябва да отидем вдясно на Applied Steps и да кликнем два пъти на Source
Тук разбираме причината за многото колони – Power Query е отворил нашия txt файл като csv документ. И на всеки ред, където има запетая е разделил информацията в отделна колона. Нямам обяснение защо прави така. Предполагам понеже Ексел, а от там и Power Query, са създадени за обработка на числа, разработчиците в Майкрософт са решили, че ще се се работи с данни в табличен вид. Кой знае?
Във всеки случай нашата работа тук е да натиснем стрелкичката отдясно на Csv Document и от менюто да изберем Text File.
Резултатът – данните ни вече са в една колона.
Когато слезем на следващата стъпка, обаче – чака ни неприятна изненада.
Какво се е случило? Ами просто Power Query е запаметило, че данните се импортират в няколко колони и си ги търси. Но след като ние се върнахме в предната стъпка и оправихме този бъг – вече няма Column2 и Power Query „гърми“. Не му се шашкайте много, а изтрийте стъпката Change Type като натиснете хикса отляво на нея. Един колега колоритно се изразяваше за тази операция така „Бий му смело хикса!“.
Разпознаване на въпросите
Сега, след като успешно импортирахме данните в една колона в Power Query, трябва да намерим начин да маркираме кои редове са въпроси и кои отговори. И също така – кой отговор на кой въпрос е. Това е много често срещан казус, който може да има най-различни решения. Обикновено търсим някакъв модел и ключови думи, които да използваме. В конкретния случай ето как подходих аз.
Най-напред добавяме една индексна колона от Add Column > Index Column > From 1
Така всеки ред получава уникален номер (или идентификатор ако предпочитате).
Веднага след това отиваме на Add Column > Custom Column и въвеждаме следната не чак толкова сложна формула:
Тя какво прави – ако текстът в първата колона съдържа думата QUESTION да ни върне като резултат думата Question като залепи към нея числото от колоната Index. В противен случай да върне празна стойност (null). За тези, които не се чувстват комфортно с М и писането на формули в Power Query, ще обърна внимание на следните неща:
- За разлика от ексел в Power Query няма функция IF. Просто се използва (подобно на VBA) if … then … else
- Отново за разлика от Ексел, тук формулите не работят с клетки, а с цели колони. Имената на колоните са оградени с квадратни скоби. Включваме ги във формулите с двоен клик на името на съответната колона в полето Available Columns отдясно. Може и да ги напишем на ръка, но не бива да забравяме за квадратните скоби.
- Функцията Text.Contains връща като резултат TRUE, ако някакъв текст съдържа определен стринг
- Подобно на Ексел можем да „слепим“ две стойности като използваме амперсанд (&). Но за разлика от Ексел, Power Query не може да слепи две разнородни стойности – в случая текст от първата колона с число от колоната Index. Затова трябва да използваме функцията Text.From, която да превърне числата в колона Index в текст
- Power Query е case sensitive! Запомнете това. Ако напишем Text.Contains([Column1], „question“), ще получим грешен резултат
Ето го резултатът от тези две стъпки. Всъщност това е втората от моите техники в Power Query – използвайки индексната колона и формулката ние всъщност направихме уникален идентификатор на всеки въпрос. Е, номерацията малко не е както трябва първият въпрос е с номер 1, но вторият – с номер 9. Но това не е толкова важно.
Идва ред на третата техника. Една проста наглед функционалност в Power Query, която улеснява толкова много работата на всеки анализатор – десен клик върху колоната Question Number > Fill down
По този начин точно определяме къде започва и къде завършва всеки въпрос.
Определяне кой отговор към кой въпрос принадлежи
Сега трябва да използваме тези две техники в Power Query, за да определим и къде започват и завършват отговорите. Първо добавяме нова колона със следната формула:
Както виждате, тя е подобна на горната, но този път ако текстът съдържа [QUESTION], резултатът e „Question…”, а ако текстът съдържа ANSWER – резултатът е “Answer of Question …”.
И понеже стана малко дълго съм използвал още един номер (четвърти по ред) – когато пиша по-сложни формули да отделям съставните им части на отделни редове, за да е по-прегледно.
А след това запълваме с Fill Down и прилагаме филтър, за да махнем излишните редове (тези, които са празни, съдържат черти и думите [QUESTION], [ANSWER]). Ето го моментният резултат на този етап:
Събиране на редове в една клетка
На последната картинка по-горе виждаме, че много точно сме подредили всеки въпрос и неговия отговор под него в колоната, наречена Custom. Но нашата крайна цел е да имаме всеки въпрос в една клетка и до него – съответният отговор пак в една клетка. Събирането на няколко реда в една клетка всъщност е най-интересната от всички техники в Power Query, които ще ви покажа с този казус. Всичко направено до тук беше подготовка за нея. Техниката е обяснена съвсем подробно от Oz du Soleil в това видео. Аз ще ви преведа през основните стъпки в картинки и на български език.
Искаме да съберем (т.е. групираме) текстовете на всеки въпрос и отговор в една клетка. Затова правим десен клик върху колоната, която съдържа уникалните идентификатори на всеки въпрос и отговор (Custom) и избираме Group By.
В този екран казваме на Power Query да: (1) Групира по колоната Custom като (2) събере стойностите в (3) колона Column1 и накрая (4) да кръсти новата колона Grouped Text
Резултатът е грешка, защото ние поискахме от Power Query да събере стойностите в колона, която съдържа текст. А Power Query може да събира само числа (както е и в Ексел между другото).
Това, което се е сетил Oz du Soleil е, че когато кликаме по менютата, Power Query създава за нас автоматично формули използвайки функциите на езика М. И ние можем да редактираме кода, като в случая подменим функцията List.Sum с Text.Combine, която вече борави именно с текст. Ето това е петата техника в Power Query – редактирането на М кода, за да го пригодим към нашите цели.
Нагледно какво се случва от тази простичка промяна:
Отделяне на редовете във всяка клетка
Сега редовете на всеки въпрос и всеки отговор са събрани в една клетка. Но резултатът не е перфектен, защото във всяка клетка тексът е една поредица от изречения. А примерно първият въпрос беше на два реда, отговорите на третия (който за момента е под номер 18) също бяха на отделни редове, а сега са в един.
За да поправим това, нека разгледаме синтаксиса на функцията Text.Combine, който е съвсем прост:
Text.Combine(Текст, разделител)
Текстът в нашия случай е Column1, но разделител не сме използвали. Нека видим какво ще се получи, ако сложим разделител наклонена черта с интервал преди и след нея ( / ):
Сега нещата изглеждат по-добре – имаме разделение между отделните части на отговорите и въпросите. Но как можем да ги направим на отделни редове?
Ето я и шестата от моите техники в Power Query за решаване на този казус. Ще използваме функцията Character.FromNumber. Тя е абсолютно аналогична на функцията CHAR в Ексел и връща символ, който съответства на определен код. И както в Ексел, на код 10 съответства нов ред. Да, за компютъра това е символ макар да не се вижда и разпечатва. И ето го крайният резултат (умишлено съм разделил формулата на редове, за да се виджда по-добре частта с Table.Combine):
Финално подреждане
Вече имаме въпросите и отговорите в една клетка и отговорът на всеки въпрос е под него. Остана ни само да ги наредим един до друг. Това e доста често срещан казус при работа с данни и едно от решенията му е нашата седма техника:
Добавяме две индексни колони. Едната да започва от 0 със стъпка 1, другата от 1 пак със стъпка 1
След това Home > Merge Queries
Следващата стъпка е именно моментът, в който правим магията.
Казваме на Power Query да обедини текущата ни заявка със … самата нея! О, даааа, това е възможно в Power Query и както ще видите – съвсем смислено. Обърнете внимание, че в горната таблица е оцветена със зелено колоната Index.1, а в долната – Index. Това се прави като кликнем върху тях и по този начин указваме кой е ключът, по който да се извърши обединението.
Когато натиснем ОК, Power Query добавя една нова колона
Колоните Index и Index.1 вече не са ни необходими, затова можем да ги маркираме и премахнем по показания начин:
Остава само да натиснем бутона отдясно на името на тази нова колона и от списъка, който се появява да изберем колоната, в която са въпросите и отговорите – Grouped Text.
Сега отиваме в колоната Custom, натискаме бутона за филтър и избираме Text Filters > Begins With
А тук задаваме, че искаме да филтрираме само тези редове, които започват с Question
И сме готови – имаме една колона с въпросите и друга с отговорите, всеки в по една клетка. От тук нататък остава да премахнем излишната вече колона Custom да сложим подходящи заглавия на двете колони и да ги заредим в Ексел с Close & Load бутона.
Обобщение
Получи се малко дълга статия, затова накратко ще обобщя седемте техники в Power Query, които използвахме:
- Заредихме данните в Power Query и още на първата стъпка променихме източника на данни от CSV Document на TXT file (за да са в една колона)
- Използвахме ключовите думи (пардон стрингове) [QUESTON], [ANSWER] и една индексна колона за да създадем уникален идентиикатор на всеки въпрос и отговор към него
- Събрахме текстовете на всички въпроси и отговори като подменихме List.Sum с Tent.Combine във формулата, създадена от Power Query при групиране на редове
- С помощта на две индексни колони – от нула и от едно и Merge Queries подредихме въпросите и отговорите в две колони една до друга