Ако използвате интензивно Ексел за обработка и анализ на данни и не сте чували за Power Query Extract, то със сигурност похабявате много нерви, време или пари, а най-вероятно и трите. В тази кратка статия ще покажа как с помощта на този инструмент на Power Query, сложни и дори невъзможни обработки в Ексел се случват с лекота.
Казусът
На долната картинка виждате една типична таблица с данни (напълно измислени), извадена от някаква счетоводна система. Таблицата има ясна структура – на всеки ред имаме номер на фактура, име на клиент, дата на фактурата и сума.
На теория разполагаме с ценни данни за това кой, кога и колко купува от нас. На практика не можем да разберем нищо за нашия бизнес преди да ги приведем във вид, в който различните данни са в отделни колони:
Проблемът
Основният проблем в случая е, че за разделител между отделните колони е използван интервал, но имената на клиентите също съдържат интервали и при това различен брой. Клиентът Симсон ВД има един интервал в името си, но „Баба Яга ЕТ“ – два, а Фондация „Храни се здравословно и спортувай“ – цели пет. Така, ако решим да използваме Text-to-columns, за да разделим (split) колоната на всеки интервал, ще получим още по-голяма каша:
Различният брой интервали в името на клиентите прави доста сложно и използването на формули или макрос.
Тази ситуация много лесно се разрешава с помощта на Power Query Extract. Той се намира в таб Transform, секция Text Column. Това невзрачно на пръв поглед бутонче крие неподозирана мощ.
Внимание! При използването на Power Query Extract от оригиналната колона остава само това, което сме извлекли. За това първо трябва да направим дубликат на колоната, като я маркираме, след това десен бутон и избираме Duplicate Column.
След това важно уточнение, нека да разгледаме с нарастваща сложност начините на използване на Extract
Номер на фактура
Да се извлече номерът на фактурата е най-лесната работа в случая. Честно казано можем да го направим дори и с функцията LEFT в Ексел. Затова е подходящо да започнем с него, за да демонстрираме възможностите на Extract.
Лесно се забелязва, че независимо от това колко символа е номерът на фактурата, винаги след него има разделител интервал. Затова ще използаме Extract -> Text Before Delimiter
Тук в полето Delimiter въвеждаме разделителя, т.е. в случая интервал:
И готово – номерът на фактурата е извлечен и на нас ни остава само да преименуваме колоната
Сума
Следващото по сложност е извличането на сумата. Тя винаги се намира в края на колоната след последния разделител.
За това дублираме колоната с данните и отиваме на Extract -> Text After Delimiter
Тук обаче простото въвеждане на интервал няма да ни свърши работа, защото в различните клетки има много и различен брой интервали преди да се стигне до сумата. Затова натискаме бутона Advanced Options и там се показват две нови полета. Горното се казва Scan for the delimiter и в него има две стойности: From the start of the input и From the end of the input. На български казано – в това поле казваме на Power Query дали да брои разделителите от началото на текста или от края на текста. В случая искаме да намерим последния разделител и да извлечем стойността, която е след него. Затова избираме From the end of the input
Резултатът е точно според очакванията.
След като сме дали подходящо име на колоната със сумите и дублираме оригиналната колона идва ред на
Датата
Логиката тук е следната – датата винаги е между двата последни разделителя (интервали). Затова избираме Extract -> Text Between Delimiters (т.е. ще извлечем текст, който е между два разделителя)
Ясно е, че за Start delimiter и End delimiter задаваме интервал, но имаме нужда и още опции.
Ако тръгнем от началото на текста – ние не знаем къде точно ще бъде първият разделител, защото имената на клиентите съдържат различен брой интервали. Но ако тръгнем от края на текста, то датата винаги е между последните два интервала. Затова в Advanced Options избираме From the end of the input.
И така – тръгваме от края на текста и искаме да намерим предпоследния, т.е. втория разделител, затова в полето Number of start delimiters to skip въвеждаме 2.
След като сме намерили началната точка , от която ще извлечем датата, трябва да тръгнем в обратната посока, т.е. към края на текста, за да намерим крайната точка. Затова в полето Scan for the end delimiter избираме From the start delimiter, toward the end of the input
Но цялата тази сложна операция си заслужава усилията, защото резултатът е точно каквото искаме:
Името на клиента
Това пусто име, което толкова обърква сметките в тази проста наглед задача, винаги се намира между първия интервал и датата, а датата винаги съдържа точка. И ето това можем да използваме по този начин:
Избираме Text Between Delimiters като първият разделител е интервал, а вторият – точка.
Резултатът от това изглежда така:
Отделили сме името и остава само дребната подробност да премахнем денят от месеца, който остана най-отдясно. За целта трябва да отделим текста, който е пред последния интервал във всяка клетка. А това не е никакъв проблем с Power Query Extract. Достатъчно е да изберем следните опции:
И сме готови
За финал подреждаме колоните и да премахваме тази с оригиналните данни
