Регулярные выражения (RegExp) в Power Query

Если вы хотя бы чуть-чуть знакомы с регулярными выражениями, то рекламировать вам их не нужно. Если же вы не совсем в теме, то регулярные выражения (Regular Expressions = RegExp = "регэкспы" = "регулярки") - это язык, где с помощью специальных символов и правил производится поиск нужных подстрок в тексте, их извлечение или замена на другой текст. Это очень мощный и красивый инструмент, на порядок превосходящий по возможностям все остальные способы работы с текстом.

Я уже подробно и с кучей примеров из жизни описывал, как можно добавить поддержку регулярных выражений в Excel с помощью несложных макросов - если вы ещё не читали эту статью, то крайне рекомендую с ней ознакомиться прежде чем продолжать. Откроете для себя много нового, гарантирую :)

Открытым, тем не менее, остается вопрос - а как добавить возможность использовать регулярные выражения в Power Query? Power Query, конечно, хороша и сама по себе и много чего умеет делать с текстом (резать, клеить, чистить и т.д.), но если бы удалось скрестить это с мощью регулярных выражений - это была бы просто бомба.

Встроенных функций для работы с RegExp'ами в Power Query, к сожалению, нет и официальная справка и техподдержка Microsoft отвечают на этот вопрос отрицательно. Однако, есть способ обойти это ограничение :)

Суть метода

Главная идея проста до безобразия.

В списке встроенных возможностей Power Query есть функция Web.Page. Описание этой функции на оф.сайте справки Microsoft предельно лаконично:

Описание функции Web.Page

В переводе это будет: "Возвращает содержимое документа HTML, разбитого на составные структуры, а также представление полного документа и его текста после удаления тегов." Так себе описание, прямо скажем.

Обычно эта функция используется при импорте данных из веба и автоматически подставляется, например, когда мы выбираем на вкладке Данные команду Из интернета (Data - From web). Мы даём функции в качестве аргумента веб-страницу, а она возвращает нам её содержимое в виде таблиц, вычистив предварительно все теги.

Что в справке НЕ написано, так это то, что помимо языка разметки HTML функция Web.Page поддерживает скрипты на языке JavaScript, который сейчас повсеместно используется на веб-сайтах в интернете. А JavaScript, в свою очередь, всегда умел работать с регулярными выражениями и имеет встроенные функции для RegExp'ов! Так что для реализации регулярок в Power Query нам нужно будет скормить функции Web.Page в качестве аргумента маленькую программку на JavaScript, которая и сделает за Power Query всю работу.

Как это выглядит на чистом JavaScript

Подробных руководств по работе с регулярными выражениями на JavaScript в интернете - масса (например, раз, два).

Если коротко и упрощенно, то код на JavaScript будет выглядет так:

Код на JavaScript

Здесь:

  • var str = 'Оплата по счетам 123 и 789 за колбасу"; - создаем переменную str и присваиваем ей исходный текст, который будем анализировать.
  • var pattern = /\d+/gi; - создаем регулярное выражение и помещаем его в переменную pattern.
    Выражение начинается знаком слэш (/).
    Само выражение здесь, для примера, это \d+ - обозначает любую последовательности цифр.
    Через дробь после выражения идут дополнительные параметры (модификаторы) поиска - их можно указывать в любом порядке:
    • g - означает глобальный поиск, т.е. после нахождения совпадения нужно не останавливаться, а продолжать поиск до конца текста. Если этот модификатор не задан, то наш скрипт выдаст только первое совпадение (123)
    • i - поиск без учёта регистра букв
    • m - многострочный поиск (применяется, когда исходный текст разбит на несколько строк)
  • var result = str.match(pattern).join(';'); - выполняем поиск в исходном тексте (str) по заданному регулярному выражению (pattern) и помещаем результаты в переменную result, сцепив их через точку с запятой с помощью команды join
  • document.write(result); - выводим на экран содержимое переменной result
Также обратите внимание, что текстовые строки (за исключением регулярного выражения) в JavaScript заключаются в апострофы, а не в кавычки, как в Power Query или VBA.

На выходе этот скрипт выдаст нам в качестве результата все числа, найденные в исходном тексте:

123;789

Краткий курс по JavaScript закончен, всем спасибо. Надеюсь, вы ухватили логику :)

Осталось перенести эту конструкцию в Power Query.

Функция поиска и извлечения текста по регулярному выражению в Power Query

Делаем следующее:

1. Открываем Excel и создаем новый пустой запрос Power Query на вкладке Данные - Получить данные / Создать запрос - Из других источников - Пустой запрос (Data - Get data / New query - From other sources - Blank query). Если у вас старая версия Excel 2010-2013 и Power Query у вас не встроена, а была установлена как отдельная надстройка, то всё это будет на вкладке Power Query, а не Данные.

2. В открывшемся пустом окне редактора запросов в правой панели сразу вводим имя нашей будущей функции (например, fxRegExpExtract)

Вводим имя функции

3. Идём на вкладку Просмотр - Расширенный редактор (View - Advanced Editor), стираем весь М-код пустого запроса и вставляем туда код нашей суперфункции:

М-код функции

Следите за руками:

В первой строке мы говорим, что в нашей функции будет три текстовых аргумента: txt - исходный анализируемый текст, regex - шаблон регулярного выражения, delim - символ-разделитель для вывода результатов.

Далее вызываем функцию Web.Page, формируя у нее в аргументе описанный выше код на JavaScript. В код вклеиваем и подставляем наши аргументы-переменные.

Фрагмент:

[Data]{0}[Children]{0}[Children]{1}[Text]{0}

... нужен, чтобы "провалиться" в нужную нам таблицу с результатами. Дело в том, что функция Web.Page в качестве результата выдает несколько вложенных друг в друга таблиц, повторяющих структуру веб-страницы. Без этого фрагмента М-кода наша функция выдавала бы на выходе это:

Результат функции Web.Page в оригинале

... и нам пришлось бы несколько раз щелкать мышью по слову Table, последовательно "проваливаясь" в дочерние вложенные таблицы в столбцах Children:

Пошаговое проваливание во вложенные таблицы

Вместо всей этой котовасии мы сразу в коде нашей функции указываем какая вложенная таблица и столбец (Text) нам нужны.

Вот, собственно, и всё секреты. Осталось нажать на кнопку Готово в окне Расширенного редактора, куда мы вставили наш код, и можно приступать к самому вкусному - пробовать нашу функцию в работе.

Вот вам пара примеров для затравки.

Пример 1. Извлекаем номер счета и дату из описания платежа

Имеем банковскую выписку с описанием (назначением) платежей, где нужно вытащить в отдельные столбцы номера и даты оплаченных счетов:

Банковская выписка

Грузим таблицу в Power Query стандартным образом через Данные - Из таблицы / диапазона (Data - From Table/Range).

Затем добавляем вычисляемый столбец с нашей функцией через Добавление столбца - Вызывать настраиваемую функцию (Add Column - Invoke Custom Function) и вводим её аргументы:

Вызываем нашу функцию

В качестве регулярного выражения (аргумент regex) используем шаблон:

(\d{3,5}|\d{2}\.\d{2}\.\d{4})

... в переводе на человеческий язык означающий: 

числа от 3 до 5 разрядов (номера счетов)

или

фрагменты вида "2-разрядное число - точка - 2-разрядное число - точка - 4-разрядное число", то бишь даты вида ДД.ММ.ГГГГ.

В качестве символа-разделителя (аргумент delim) вводим точку с запятой.

После нажатия на ОК наша волшебная функция анализирует все исходные данные по нашему регулярному выражению и формирует нам столбец с найденными номерами и датами счетов:

Полученный столбец с номерами и датами счетов

Останется его разделить по точке с запятой с помощью команды Главная - Разделить столбец - По разделителю (Home - Split column - By delimiter) и мы получим то, что хотели:

Готовый результат

Красота!

Пример 2. Извлекаем адреса эл.почты из текста

Предположим, что у нас в качестве исходных данных есть вот такая таблица:

Исходные данные

... откуда нам нужно вытащить встречающиеся там адреса эл.почты (для наглядности я выделил их в тексте красным).

Как и в прошлом примере, грузим таблицу в Power Query стандартным образом через Данные - Из таблицы / диапазона (Data - From Table/Range).

Затем добавляем вычисляемый столбец с нашей функцией через Добавление столбца - Вызывать настраиваемую функцию (Add Column - Invoke Custom Function) и вводим её аргументы:

Вызываем функцию fxRegExpExtract

Парсинг адресов эл.почты - задача посложнее и для её решения есть куча регулярок разной степени кошмарности. Я использовал один из простых вариантов - не идеальный, но вполне работающий в большинстве случаев:

[\w|.|-]*@\w*\.[\w|.]*

В качестве разделителя (delim) можно ввести точку с запятой и пробел.

Жмём на ОК и получаем столбец с извлеченными из исходной текстовой "каши" адресами эл.почты:

Извлеченные email в Power Query

Магия!

P.S.

Как говорится: "нет такой хорошей вещи, которую нельзя было бы сделать ещё лучше". Power Query и сам-то по себе крут, а уж в сочетании с регулярными выражениями даёт нам совершенно нереальную мощь и гибкость в обработке любых текстовых данных. Надеюсь, Microsoft когда-нибудь добавит поддержку RegExp в обновлениях Power Query и Power BI и все описанные выше танцы с бубном останутся в прошлом. Ну, а пока - так.

Также вдогон хочу добавить, что с регулярными выражениями удобно играться на сайте https://regexr.com/ - прямо в онлайн-редакторе. Там же в разделе Community Patterns есть огромное количество готовых регулярок на все случаи жизни. Экспериментируйте - вся мощь регулярных выражений теперь к вашим услугам и в Power Query!

Ссылки по теме



18.07.2020 13:42:07
супер! спасибо большое!
21.07.2020 11:29:10
Николай, очень круто, спасибо. Но есть один нюанс. При использовании такой функции, она может медленно обрабатывать очень большой массив. Т.к. для каждой строки, регулярки будут каждый раз инициализироваться по-новой. В таком случае для передачи в функцию столбца можно сначала объединить его значения в одну строку через разделитель, а в скрипте уже обрабатывать огромную строку активировав функцию только один раз. После получения результата разодрать строку обратно в список и прицепить к исходной таблице. Андрей VG на форуме показывал такой подход - работает очень шустро. Жаль ссылку не могу найти.
23.07.2020 23:44:50
Позволю себе дополнить к части статьи к использованию регулярных выражений в Power Query через javascript.
Перед тем как передавать текст для x, delim в функцию следует, чтобы избежать ошибок выполнить замены в тексте:
Самым первым символ \ заменить на \\
с учётом применённых кавычек для текста в js ' на \'
#(lf) на \n
#(cr) на \r
#(tab) на \t
Или всё сразу функцией
let
    getJsSafeText = (pqText) => List.Accumulate({{"\", "\\"}, {"'", "\'"}, {"#(tab)", "\t"}, {"#(lf)", "\n"}, {"#(cr)", "\r"}}, pqText, (acc, pair) => Text.Replace(acc, pair{0}, pair{1})),
    example = getJsSafeText("'В кавычках'#(lf)перехос#(cr)и табуляция#(tab)\текст\")
in
    example
 


иначе можно получить проблемы.
Если в регулярном выражении используется символ /, то следует указывать \/, иначе тоже можно получить проблемы. Полный список escape-символов тут.
24.07.2020 13:27:33
Андрей, подскажите как вынести регулярку из js-скрипта. То есть, как у Николая Павлова, передавать регулярное выражение при вызове функции переменной. В коде эту часть - (\d{3,5}|\d{2}\.\d{2}\.\d{4}) - хотел бы видеть здесь - fn(text, "(\d{3,5}|\d{2}\.\d{2}\.\d{4})",  " ; ";)
let fn = (txt as text, delim as text ) =>
      Web.Page("<script>
        var delim = '" & delim & "'; var d = '" & txt & "', r = d.split('|'), 
        c = r.length; for (var i = 0; i < c; i += 1) {try {r[i] = 
        r[i].match(/(\d{3,5}|\d{2}\.\d{2}\.\d{4})(?= |$)/gi).join(delim).replace(/\//g, '');} 
      catch (e) {r[i] = '';};};document.write(r.join('|'));</script>"){0}[Data]{0}[Children]{1}[Children]{0}[Text],

    
    Source = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    data = let text=try Text.Combine(Source[#"Назначение платежа"], "|") otherwise null,
    convert = fn(text, " ; "),
    resultCol = try Table.FromColumns({Text.Split(convert, "|")}, {"test"}) otherwise null,
    resultIdx = Table.AddIndexColumn(resultCol, "idx"),
    sourceIdx = Table.AddIndexColumn(Source, "idx"),
    joined = Table.Join(sourceIdx, {"idx"}, resultIdx, {"idx"})
    in Table.RemoveColumns(joined,{"idx"})
in data
 
24.07.2020 13:52:42
Михаил, не уловил. А что помешало сделать как у Николая?
27.07.2020 10:42:35
Михаил, собирать таблицу не обязательно через связку индекса и джойна, достаточно разобрать таблицу на столбцы Table.ToColumns к получившемуся списку через & добавить новый обработанный столбец и собрать обратно через Table.FromColumns. Так оно и шустрее бегает.
27.07.2020 18:21:42
И вправду! Так даже симпатишнее:)
 let Source=Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],fn = (txt as text, regExp as text, delim as text ) => let 
       i=List.Accumulate                                                                         ({{"\", "\\"}, {"'", "\'"},
     {"""", " \\""\\ "},                                                                         {", ", " \\, \\ "},
       {"#(tab)", "\t"},                                                                         {"#(lf)", "\n"}, 
       {"#(cr)", "\r"}},                                                                         txt, (acc, pair) => 
       Text.Replace(acc,                                                                         pair{0}, pair{1})) in
      Web.Page("<script>                                                                         var delim = '" & delim & "';
         var regExp = '"                                                                         & regExp & "'; 
    var d = '" & i & "',                                                                         r = d.split('|'), c = 
           r.length; for                                                                         (var i = 0; i < c; 
            i += 1) {try                                                                         {r[i] = r[i].match
             (/"&regExp&                                                                         "(?= |$)/gi).join
         (delim).replace                                                                         (/\//g, '');} catch 
     (e) {r[i] = '';};};                                                                         document.write(r.join('|'));
    </script>"){0}[Data]                                                                         {0}[Children]{1}[Children]
            {0}[Text] in                                                                         Table.SplitColumn(
       Table.FromColumns                                                                         (Table.ToColumns
    (Source)&{Text.Split                                                                         (fn(try Text.Combine(Source
[#"Назначение платежа"],                                                                         "|") otherwise null,
"(\d{3,5}|\d{2}\.\d{2}\.\d{4})"," ; "), "|")},{"Назначение платежа","i"}),"i",(i)=>Text.Split(i,";"),{"Номер счета","Дата счета"})
12.08.2020 20:03:44
Ну, я-то предполагал, что подготовить корректное регулярное выражение с заэкранированными символами и т.д. - это забота пользователя :)
24.07.2020 15:57:00
Андрей, точно!
Сделал по примеру Николая Павлова
Наверх