Съдържание
Ако сте поне малко запознати с регулярните изрази, тогава не е нужно да ги рекламирате. Ако не сте съвсем в темата, тогава регулярните изрази (Regular Expressions = RegExp = “regexps” = “regulars”) е език, при който с помощта на специални символи и правила се търсят необходимите поднизове в текста, те се извличат или заменен с друг текст. Това е много мощен и красив инструмент, с порядък по-добър от всички други начини за работа с текст.
Вече описах подробно и с куп примери от живота как можете да добавите поддръжка на регулярни изрази към Excel с помощта на прости макроси – ако не сте чели тази статия, горещо ви препоръчвам да я прочетете, преди да продължите. Ще откриеш много нови неща, гарантирам 🙂
Остава отворен обаче въпросът – как да добавим възможност за използване на регулярни изрази в Power Query? Power Query, разбира се, е добър сам по себе си и може да направи много с текст (изрязване, залепване, почистване и т.н.), но ако можете да го пресечете със силата на регулярните изрази, ще бъде просто бомба.
За съжаление в Power Query няма вградени функции за работа с RegExps, а официалната помощ и техническа поддръжка на Microsoft отговарят отрицателно на този въпрос. Има обаче начин да се заобиколи това ограничение 🙂
Същността на метода
Основната идея е проста за опозоряване.
В списъка с вградени възможности на Power Query има функция Уеб страница. Описанието на тази функция в официалния помощен сайт на Microsoft е изключително кратко:
Преведено, това би било: „Връща съдържанието на HTML документа, разбито на съставните му структури, както и представяне на целия документ и тялото му, след като етикетите са били премахнати.“ Такова описание, честно казано.
Обикновено тази функция се използва при импортиране на данни от мрежата и автоматично се замества, например, когато избираме в раздела Дата Команда От Интернет (Данни — От мрежата). Даваме на функцията уеб страница като аргумент и тя ни връща съдържанието й под формата на таблици, като преди това е изчистила всички тагове.
Това, което НЕ се казва в помощта, е, че в допълнение към езика за маркиране на HTML функция Уеб страница поддържа JavaScript скриптове, който сега е навсякъде в уебсайтовете в Интернет. А JavaScript от своя страна винаги е можел да работи с регулярни изрази и има вградени функции за RegExps! Така че, за да внедрим регулярни изрази в Power Query, ще трябва да захраним функциите Web.Page като аргумент на малка JavaScript програма, която ще свърши цялата работа за Power Query.
Как изглежда в чист JavaScript
В интернет има много подробни уроци за работа с регулярни изрази в JavaScript (например едно, две).
Накратко и опростено, JavaScript кодът ще изглежда така:
Тук:
- var str = 'Плащане на сметки 123 и 789 за наденица'; – създаване на променлива ул. и му задайте изходния текст, който ще анализираме.
- променлив модел = /d+/gi; – създайте регулярен израз и го поставете в променлива модел.
Изразът започва с наклонена черта (/).
Самият израз тук например е d+ означава произволна последователност от цифри.
Чрез дробта след израза има допълнителни параметри за търсене (модификатори) – те могат да бъдат посочени в произволен ред:
- g – означава глобално търсене, т.е след намиране на съвпадение не трябва да спирате, а да продължите търсенето до края на текста. Ако този модификатор не е зададен, нашият скрипт ще върне само първото съвпадение (123)
- i – търсене без оглед на регистъра на буквите
- m – многоредово търсене (използва се, когато изходният текст е разделен на няколко реда)
- var result = str.match(pattern).join(';'); – извършете търсене в изходния текст (ул.) чрез дадения регулярен израз (модел) и поставете резултатите в променлива резултат, като ги свързвате с точка и запетая с помощта на командата присъединят към
- document.write(резултат); – показване на съдържанието на променливата резултат
Също така имайте предвид, че текстовите низове (с изключение на регулярните изрази) в JavaScript са затворени в апостроф, а не в кавички, както е в Power Query или VBA.
На изхода този скрипт ще ни даде като резултат всички числа, намерени в изходния текст:
123, 789
Краткият курс по JavaScript приключи, благодаря на всички. Надявам се, че разбирате логиката 🙂
Остава да прехвърлите тази конструкция в Power Query.
Функция за търсене и извличане на текст чрез регулярен израз в Power Query
Ние правим следното:
1. Отворете Excel и създайте нов празен Power Query в раздела Данни – Вземете данни / Създайте заявка – От други източници – Празна заявка (Данни — Получаване на данни / Нова заявка — От други източници — Празна заявка). Ако имате стара версия на Excel 2010-2013 и Power Query нямате вградена, а е инсталирана като отделна добавка, тогава всичко това ще бъде в раздела Запитване за захранванеИ не Дата.
2. В празния прозорец на редактора на заявки, който се отваря, в десния панел, незабавно въведете името на нашата бъдеща функция (например, fxRegExpExtract)
3. Да отидем на раздела Изглед – Разширен редактор (Преглед — Разширен редактор), изтриваме целия M-код на празната заявка и поставяме кода на нашата суперфункция там:
Гледайте ръцете си:
В първия ред казваме, че нашата функция ще има три текстови аргумента: TXT – оригиналният текст, който се анализира, регулярен – модел на регулярен израз, разделител — разделителен знак за показване на резултатите.
След това извикваме функцията Уеб страница, образувайки JavaScript кода, описан по-горе в своя аргумент. Ние поставяме и заместваме нашите променливи аргументи в кода.
фрагмент:
[Данни]{0}[Деца]{0}[Деца]{1}[Текст]{0}
… е необходимо, за да „попаднем“ в таблицата с нужните резултати. Въпросът е, че функцията Уеб страница в резултат на това създава няколко вложени таблици, които повтарят структурата на уеб страница. Без тази част от M-код, нашата функция ще изведе това:
… и ще трябва да кликнем върху думата няколко пъти Маса, последователно „пропадащи“ в дъщерни вложени таблици в колони Деца:
Вместо целия този цитат, ние незабавно посочваме в кода на нашата функция коя вложена таблица и колона (Текст) Имаме нужда от.
Тук всъщност са всички тайни. Остава да натиснете бутона завършеност в прозореца разширен редактор, където вмъкнахме нашия код, и можете да продължите към най-вкусното – опитайте нашата функция на работа.
Ето няколко примера за семена.
Пример 1. Извличане на номер на сметка и дата от описанието на плащането
Имаме банково извлечение с описание (цел) на плащанията, където трябва да извадите номерата и датите на платените фактури в отделни колони:
Зареждаме таблицата в Power Query по стандартния начин Данни – От таблица/диапазон (Данни - от Тспособен/Рангел).
След това добавяме изчислена колона с нашата функция чрез Добавяне на колона – извикване на персонализирана функция (Добавяне на колона — извикване на персонализирана функция) и въведете неговите аргументи:
Като регулярен израз (аргумент регулярен) шаблон, който използваме:
(d{3,5}|d{2}.d{2}.d{4})
… преведено на човешки език означава:
числа от 3 до 5 цифри (номера на сметки)
or
фрагменти от формата „2-битово число – точка – 2-битово число – точка – 4-битово число“, тоест дати във формата ДД.ММ.ГГГГ.
Като разделителен знак (аргумент разделител) въведете точка и запетая.
След като кликнете върху OK нашата магическа функция анализира всички първоначални данни според нашия регулярен израз и формира колона за нас с намерените номера и дати на фактурите:
Остава да го разделите с точка и запетая с помощта на командата Начало — Разделяне на колона — По разделител (Начало — Разделяне на колона — По разделител) и получаваме това, което искахме:
Красота!
Пример 2: Извличане на имейл адреси от текст
Да предположим, че имаме следната таблица като първоначални данни:
… откъдето трябва да извадим намерените там имейл адреси (за по-голяма яснота ги подчертах в червено в текста).
Както в предишния пример, зареждаме таблицата в Power Query по стандартния начин чрез Данни – От таблица/диапазон (Данни - от Тспособен/Рангел).
След това добавяме изчислена колона с нашата функция чрез Добавяне на колона – извикване на персонализирана функция (Добавяне на колона — извикване на персонализирана функция) и въведете неговите аргументи:
Разборът на имейл адреси е по-трудна задача и има куп регулярни изрази с различна степен на кошмар за решаването й. Използвах една от простите опции – не идеална, но доста работеща в повечето случаи:
[w|.|-]*@w*.[w|.]*
Като разделител (разделител) можете да въведете точка и запетая и интервал.
Кликнете върху OK и получаваме колона с имейл адреси, извлечени от оригиналния текст „каша“:
Магия!
PS
Както се казва: „Няма толкова добро нещо, което да не може да бъде направено още по-добро“. Power Query е страхотен сам по себе си и когато се комбинира с регулярни изрази, ни дава напълно нереалистична мощност и гъвкавост при обработката на всякакви текстови данни. Надявам се някой ден Microsoft да добави поддръжка на RegExp в актуализациите на Power Query и Power BI и всички горепосочени танци с дайре ще останат в миналото. Е, засега да.
Също така искам да добавя, че е удобно да играете с регулярни изрази на сайта https://regexr.com/ - точно в онлайн редактора. Там в секцията Общностни модели Има огромен брой готови редовни сезони за всички поводи. Експериментирайте – цялата сила на регулярните изрази вече е на ваше разположение в Power Query!
- Какво представляват регулярните изрази (RegExp) и как да ги използвате в Excel
- Търсене на размит текст в Power Query
- Сглобяване на таблици от различни файлове с помощта на Power Query