Регулярные выражения (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 на форуме показывал такой подход - работает очень шустро. Жаль ссылку не могу найти.
12.04.2023 19:36:05
Вероятно этот комментарий имелся ввиду.
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
Андрей, точно!
Сделал по примеру Николая Павлова
03.10.2020 10:34:17
Просто оставлю ссылкус регулярками для эл.почты.
15.12.2020 16:22:02
У меня достает только первле совпадение из строки, что я делаю не так, не могу понять.

строка S10K090861;S10K062489;S10K090859;S10C210542;S10K112778;S10J172483;S10A190852;S10K062485;S10K090827;
формула = Table.AddColumn(#"Converted to Table", "fxRegExpExtract", each fxRegExpExtract([Column1], "(^[A-Z]{1}[0-9]{2}[A-Z]{1}[0-9]{6})", ";"))
14.02.2021 11:56:40
пытаюсь воспроизвести текст функции и получаю ошибку в виде запятой в первой строке (http/joxi.ru/xAeX3zyiXEJLg2)
не могу понять, где ошибаюсь. Кто-нить не подскажете?
29.04.2021 02:42:10
Николай, спасибо, очень полезная информация.
Посоветуйте как лучше сделать извлечение данных из прайсов разных поставщиков смартфонов - у каждого свой формат и расположение данных в ОДНОЙ строке, а значит расчётом по разделителям не обойтись.  Вот пример:
Samsung G998 Galaxy S21 Ultra 5G  6,8" 12/128Gb Серебристый Фантом  76500
Xiaomi Redmi 9T 4/128Gb Blue 🇷🇺

каждое слово в этих строчках может быть расположено в другом порядке - у каждого поставщика, а часть информации отсутствовать, например: S21 Ultra 128 - подразумевает ту же модель из 1 строчки примера.

Может разделить задачу на подзадачи?:

Первая подзадача:
извлечение данных в отдельный лист и использовать его как базу данных со следующими столбцами:
Brand; МодельНоменклатурная; МодельРекламная; ОбъёмПамяти; Цвет; Цена
- Сохранение этих данных в 6 столбцов(база данных регулярок)

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

Ушел от идеи форматирование исходного текста формулами т.к. часто меняются местами написание модели и формат, а так же присутствуют технические символы выделяющие "горячие предложения" и т.п.

Уже весь интернет перерыл, пробовал разные парсеры, но все они в основном перебирают столбцы и склеивают на их основе данные.
Что-нибудь посоветуете ?
S M
21.07.2021 11:19:34
Сделала по инструкции...

Но в столбце вместо результата ошибки с пояснением:

Expression.Error: Элементов в перечислении было недостаточно для выполнения операции.
Сведения:
   [List]
12.09.2023 11:19:43
тоже самое
20.10.2021 21:36:53
Подскажите пожалуйста как лучше реализовать.
Имеется НЕтабличный пдф.
В нем в определенном порядке содержаться данные: Артикул, восьмизначный код, количество, цена, стоимость, единицы измерения и т.д.
Есть таблица в экселе, в которой в столбик стоят артикулы, в том же порядке. Необходимо вытащить из ПДФ остальную информацию соответствующую каждому артикулу.
22.10.2021 11:59:36
Николай Павлов, а можно обновить код или сделать альтернативный код на php?
Беда в том, что выражение ?<= не работает. Например
(?<=Y)X 

Насколько нагуглил - у JS нет поддержки Lookbehind (Позитивный просмотр назад).
Хотя тут предлагают "Попробуйте \\1", но так и не разъяснили как и что.
Относительно поддержки может поможет сравнительная таблица
Помогите пжлст, а то не так все просто, когда знаешь)
29.03.2022 15:21:51
Подскажите, где ошибка?
В столбце вместо результата ошибки с пояснением:
Expression.Error: Элементов в перечислении было недостаточно для выполнения операции.
Сведения:
  [List]

let   fx=(txt as text, regex as text)=>
    Web.Page(
        "<script>
            var x = '" & txt & "';
            var pattern = /" & regex & "/gi;
            var result = x.replace(pattern,"");
            document.write(result);
        </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0}

in
fx
 
19.05.2022 12:20:10
Добрый день.
Expression.Error: Не удается преобразовать значение null в тип Text.
А как сделать исключение чтобы вместо ошибок получать пустое значение???
23.06.2022 10:06:16
Функционал удобный, помог не раз, но я столкнулся с проблемой - нужно было найти нужный текст в большой таблице на 350 000 стр., текст находится где то в 60% строк, остальное null. При добавлении в модель данных в pwer pivot обновление запросы идет около 1,5 часа.  А можно как то оптимизировать процесс?
12.09.2023 11:23:38
Жаль, что в моём Power Query не оказалось кнопки "Вызвать настраиваемую функцию" (Excel 2016)
Наверх