Содержание:
Если вы хоть немного знакомы с регулярными выражениями, то вам не нужно их афишировать. Если вы не совсем в теме, то регулярные выражения (Regular Expressions = RegExp = «regexps» = «regulars») — это язык, где с помощью специальных символов и правил ищутся в тексте нужные подстроки, извлекаются из них. или заменен другим текстом. Это очень мощный и красивый инструмент, на порядок превосходящий все остальные способы работы с текстом.
Я уже подробно и с кучей примеров из жизни описал, как можно добавить поддержку регулярных выражений в Excel с помощью простых макросов — если вы не читали эту статью, настоятельно рекомендую прочитать ее, прежде чем продолжить. Вы откроете для себя много нового, гарантирую 🙂
Однако остается открытым вопрос — как добавить возможность использования регулярных выражений в Power Query? Power Query, конечно, хорош сам по себе и умеет многое с текстом (вырезать, склеивать, очищать и т. д.), но если бы его можно было скрестить с мощью регулярных выражений, это была бы просто бомба.
К сожалению, встроенных функций для работы с RegExps в Power Query нет, а официальная справка и техническая поддержка Microsoft отвечают на этот вопрос отрицательно. Однако есть способ обойти это ограничение 🙂
Суть метода
Основная идея проста до безобразия.
В списке встроенных возможностей Power Query есть функция Веб-страница. Описание этой функции на официальном справочном сайте Microsoft предельно лаконичное:
В переводе это будет выглядеть так: «Возвращает содержимое HTML-документа, разбитое на составные структуры, а также представление всего документа и его тела после удаления тегов». Так себе описание, честно говоря.
Обычно эта функция используется при импорте данных из сети и автоматически подставляется, например, когда мы выбираем на вкладке Данные Command Из Интернета (Данные — из Интернета). Мы передаем функции в качестве аргумента веб-страницу, и она возвращает нам ее содержимое в виде таблиц, предварительно очистив все теги.
О чем НЕ говорится в справке, так это о том, что помимо языка разметки 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(шаблон).join(';'); – выполнить поиск в исходном тексте (ул) по заданному регулярному выражению (описания) и поместите результаты в переменную результат, объединяя их точкой с запятой с помощью команды присоединиться
- document.write(результат); – отобразить содержимое результирующей переменной
Также обратите внимание, что текстовые строки (за исключением регулярных выражений) в JavaScript заключаются в апострофы, а не в кавычки, как в Power Query или VBA.
На выходе этот скрипт выдаст нам в результате все числа, найденные в исходном тексте:
123, 789
Краткий курс JavaScript окончен, всем спасибо. Надеюсь, вы поняли логику 🙂
Осталось перенести эту конструкцию в Power Query.
Функция поиска и извлечения текста с помощью регулярного выражения в Power Query
Мы делаем следующее:
1. Откройте Excel и создайте на вкладке новый пустой Power Query. Данные – Получить данные/Создать запрос – Из других источников – Пустой запрос (Данные — Получить данные / Новый запрос — Из других источников — Пустой запрос). Если у вас старая версия Excel 2010-2013 и Power Query у вас не встроенный, а был установлен как отдельная надстройка, то все это будет на вкладке Power QueryИ не Данные.
2. В открывшемся пустом окне редактора запросов на правой панели сразу вводим имя нашей будущей функции (например, fxRegExpExtract)
3. Перейдем на вкладку Просмотр – расширенный редактор (Вид — Расширенный редактор), стираем весь М-код пустого запроса и вставляем туда код нашей суперфункции:
Следите за своими руками:
В первой строке мы говорим, что наша функция будет иметь три текстовых аргумента: текст – анализируемый исходный текст, регулярное выражение – шаблон регулярного выражения, DELIM — символ-разделитель для отображения результатов.
Далее мы вызываем функцию Веб-страница, формируя код JavaScript, описанный выше в его аргументе. Вставляем и подставляем в код наши переменные аргументы.
Фрагмент:
[Данные]{0}[Дети]{0}[Дети]{1}[Текст]{0}
… нужно, чтобы «провалиться» в таблицу с нужными нам результатами. Дело в том, что функция Веб-страница в результате создается несколько вложенных таблиц, повторяющих структуру веб-страницы. Без этого фрагмента M-кода наша функция вывела бы следующее:
… и нам пришлось бы нажать на слово несколько раз Настольные, последовательно «проваливаясь» в дочерние вложенные таблицы по столбцам Дети:
Вместо всех этих цитат мы сразу указываем в коде нашей функции какая вложенная таблица и столбец (Текст) нам нужно.
Вот, собственно, и все секреты. Осталось нажать кнопку Завершить в окне продвинутый редактор, куда мы вставили наш код, и можно приступить к самому вкусному — попробовать нашу функцию в работе.
Вот несколько примеров семян.
Пример 1. Получение номера счета и даты из описания платежа
Имеем выписку из банка с описанием (назначением) платежей, где нужно в отдельные графы вынести номера и даты оплаченных счетов:
Загружаем таблицу в Power Query стандартным способом через Данные – из таблицы/диапазона (Данные — из Тспособный/рангел).
Затем мы добавляем вычисляемый столбец с нашей функцией через Добавить столбец – вызвать пользовательскую функцию (Добавить столбец — вызвать пользовательскую функцию) и введите его аргументы:
В качестве регулярного выражения (аргумент регулярное выражение) шаблон, который мы используем:
(д{3,5}|д{2}.д{2}.д{4})
… в переводе на человеческий язык означает:
номера от 3 до 5 цифр (номера счетов)
or
фрагменты вида «2-битное число – точка – 2-битное число – точка – 4-битное число», то есть даты в формате ДД.ММ.ГГГГ.
В качестве символа-разделителя (аргумент DELIM) введите точку с запятой.
После нажатия на OK наша волшебная функция анализирует все исходные данные по нашему регулярному выражению и формирует для нас столбец с найденными номерами и датами счетов:
Осталось отделить его точкой с запятой с помощью команды Главная — Разделить столбец — По разделителю (Главная — Разделить столбец — По разделителю) и получаем то, что хотели:
Красота!
Пример 2. Извлечение адресов электронной почты из текста
Предположим, что в качестве исходных данных у нас есть следующая таблица:
…откуда нам нужно вытащить найденные там адреса электронной почты (для наглядности я выделил их в тексте красным).
Как и в предыдущем примере, загружаем таблицу в Power Query стандартным способом через Данные – из таблицы/диапазона (Данные — из Тспособный/рангел).
Затем мы добавляем вычисляемый столбец с нашей функцией через Добавить столбец – вызвать пользовательскую функцию (Добавить столбец — вызвать пользовательскую функцию) и введите его аргументы:
Парсинг адресов электронной почты — более сложная задача и для ее решения есть куча регулярных выражений разной степени кошмарности. Я использовал один из простых вариантов – не идеальный, но вполне работающий в большинстве случаев:
[w|.|-]*@w*.[w|.]*
В качестве разделителя (DELIM) вы можете ввести точку с запятой и пробел.
Нажмите на OK и получаем столбец с адресами электронной почты, извлеченный из исходного текста «каша»:
Магия!
PS
Как говорится: «Нет такой хорошей вещи, которую нельзя было бы сделать еще лучше». Power Query сам по себе крут, а в сочетании с регулярными выражениями дает нам совершенно нереальную мощь и гибкость при обработке любых текстовых данных. Надеюсь, Microsoft когда-нибудь добавит поддержку RegExp в обновлениях Power Query и Power BI и все вышеперечисленные танцы с бубном уйдут в прошлое. Ну, на данный момент, да.
Еще хочу добавить, что с регулярными выражениями удобно играть на сайте https://regexr.com/ — прямо в онлайн-редакторе. Там в разделе Шаблоны сообщества Существует огромное количество готовых регулярок на все случаи жизни. Экспериментируйте – вся мощь регулярных выражений теперь к вашим услугам в Power Query!
- Что такое регулярные выражения (RegExp) и как их использовать в Excel
- Нечеткий текстовый поиск в Power Query
- Сборка таблиц из разных файлов с помощью Power Query