Регулярные выражения (RegExp) в Power Query
Если вы хотя бы чуть-чуть знакомы с регулярными выражениями, то рекламировать вам их не нужно. Если же вы не совсем в теме, то регулярные выражения (Regular Expressions = RegExp = "регэкспы" = "регулярки") - это язык, где с помощью специальных символов и правил производится поиск нужных подстрок в тексте, их извлечение или замена на другой текст. Это очень мощный и красивый инструмент, на порядок превосходящий по возможностям все остальные способы работы с текстом.
Я уже подробно и с кучей примеров из жизни описывал, как можно добавить поддержку регулярных выражений в Excel с помощью несложных макросов - если вы ещё не читали эту статью, то крайне рекомендую с ней ознакомиться прежде чем продолжать. Откроете для себя много нового, гарантирую :)
Открытым, тем не менее, остается вопрос - а как добавить возможность использовать регулярные выражения в Power Query? Power Query, конечно, хороша и сама по себе и много чего умеет делать с текстом (резать, клеить, чистить и т.д.), но если бы удалось скрестить это с мощью регулярных выражений - это была бы просто бомба.
Встроенных функций для работы с RegExp'ами в Power Query, к сожалению, нет и официальная справка и техподдержка Microsoft отвечают на этот вопрос отрицательно. Однако, есть способ обойти это ограничение :)
Суть метода
Главная идея проста до безобразия.
В списке встроенных возможностей Power Query есть функция Web.Page. Описание этой функции на оф.сайте справки Microsoft предельно лаконично:
В переводе это будет: "Возвращает содержимое документа HTML, разбитого на составные структуры, а также представление полного документа и его текста после удаления тегов." Так себе описание, прямо скажем.
Обычно эта функция используется при импорте данных из веба и автоматически подставляется, например, когда мы выбираем на вкладке Данные команду Из интернета (Data - From web). Мы даём функции в качестве аргумента веб-страницу, а она возвращает нам её содержимое в виде таблиц, вычистив предварительно все теги.
Что в справке НЕ написано, так это то, что помимо языка разметки HTML функция Web.Page поддерживает скрипты на языке JavaScript, который сейчас повсеместно используется на веб-сайтах в интернете. А JavaScript, в свою очередь, всегда умел работать с регулярными выражениями и имеет встроенные функции для RegExp'ов! Так что для реализации регулярок в Power Query нам нужно будет скормить функции Web.Page в качестве аргумента маленькую программку на JavaScript, которая и сделает за Power Query всю работу.
Как это выглядит на чистом 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
На выходе этот скрипт выдаст нам в качестве результата все числа, найденные в исходном тексте:
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 в качестве результата выдает несколько вложенных друг в друга таблиц, повторяющих структуру веб-страницы. Без этого фрагмента М-кода наша функция выдавала бы на выходе это:
Вместо всей этой котовасии мы сразу в коде нашей функции указываем какая вложенная таблица и столбец (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) и вводим её аргументы:
Парсинг адресов эл.почты - задача посложнее и для её решения есть куча регулярок разной степени кошмарности. Я использовал один из простых вариантов - не идеальный, но вполне работающий в большинстве случаев:
[\w|.|-]*@\w*\.[\w|.]*
В качестве разделителя (delim) можно ввести точку с запятой и пробел.
Жмём на ОК и получаем столбец с извлеченными из исходной текстовой "каши" адресами эл.почты:
Магия!
P.S.
Как говорится: "нет такой хорошей вещи, которую нельзя было бы сделать ещё лучше". Power Query и сам-то по себе крут, а уж в сочетании с регулярными выражениями даёт нам совершенно нереальную мощь и гибкость в обработке любых текстовых данных. Надеюсь, Microsoft когда-нибудь добавит поддержку RegExp в обновлениях Power Query и Power BI и все описанные выше танцы с бубном останутся в прошлом. Ну, а пока - так.
Также вдогон хочу добавить, что с регулярными выражениями удобно играться на сайте https://regexr.com/ - прямо в онлайн-редакторе. Там же в разделе Community Patterns есть огромное количество готовых регулярок на все случаи жизни. Экспериментируйте - вся мощь регулярных выражений теперь к вашим услугам и в Power Query!
Ссылки по теме
- Что такое регулярные выражения (RegExp) и как использовать их в Excel
- Нечёткий текстовый поиск в Power Query
- Сборка таблиц из разных файлов с помощью Power Query
Перед тем как передавать текст для 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-символов
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 datalet 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 (/"®Exp& "(?= |$)/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,";"),{"Номер счета","Дата счета"})Сделал по примеру Николая Павлова
строка 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})", ";"))
не могу понять, где ошибаюсь. Кто-нить не подскажете?
Посоветуйте как лучше сделать извлечение данных из прайсов разных поставщиков смартфонов - у каждого свой формат и расположение данных в ОДНОЙ строке, а значит расчётом по разделителям не обойтись. Вот пример:
Samsung G998 Galaxy S21 Ultra 5G 6,8" 12/128Gb Серебристый Фантом 76500
Xiaomi Redmi 9T 4/128Gb Blue 🇷🇺
каждое слово в этих строчках может быть расположено в другом порядке - у каждого поставщика, а часть информации отсутствовать, например: S21 Ultra 128 - подразумевает ту же модель из 1 строчки примера.
Может разделить задачу на подзадачи?:
Первая подзадача:
извлечение данных в отдельный лист и использовать его как базу данных со следующими столбцами:
Brand; МодельНоменклатурная; МодельРекламная; ОбъёмПамяти; Цвет; Цена
- Сохранение этих данных в 6 столбцов(база данных регулярок)
Вторая подзадача:
- сделать поиск данных в том же исходном тексте и валидацию его через созданную ранее базу регулярок.
- поскольку данные от поставщиков дополняются - первая подзадача должны повторяться - добавление текстовых данных, которые отсутствуют в базе данных.
Ушел от идеи форматирование исходного текста формулами т.к. часто меняются местами написание модели и формат, а так же присутствуют технические символы выделяющие "горячие предложения" и т.п.
Уже весь интернет перерыл, пробовал разные парсеры, но все они в основном перебирают столбцы и склеивают на их основе данные.
Что-нибудь посоветуете ?
Но в столбце вместо результата ошибки с пояснением:
Expression.Error: Элементов в перечислении было недостаточно для выполнения операции.
Сведения:
[List]
Имеется НЕтабличный пдф.
В нем в определенном порядке содержаться данные: Артикул, восьмизначный код, количество, цена, стоимость, единицы измерения и т.д.
Есть таблица в экселе, в которой в столбик стоят артикулы, в том же порядке. Необходимо вытащить из ПДФ остальную информацию соответствующую каждому артикулу.
Беда в том, что выражение ?<= не работает. Например
Насколько нагуглил - у JS нет поддержки Lookbehind (Позитивный просмотр назад).
Хотя предлагают "Попробуйте \\1", но так и не разъяснили как и что.
Относительно поддержки может поможет
Помогите пжлст, а то не так все просто, когда знаешь)
В столбце вместо результата ошибки с пояснением:
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 fxExpression.Error: Не удается преобразовать значение null в тип Text.
А как сделать исключение чтобы вместо ошибок получать пустое значение???
1. Скорее всего, у вас проблемы с регулярным выражением - javascript известен своей капризностью к кавычкам.
Если вы не программируете ни на каком языке - самостоятельно не отладите, и надо просить помощь.
Отлаживать ваш javascript лучше через консоль браузера (F12 -> Console), а не через сторонние сайты - меньше шансов на искажение результата.
2. Может быть полезно в функции WebPage изменить вывод с
[Data]{0}[Children]{0}[Children]{1}[Text]{0}на
[Data]{0}В результате у вас в настраиваемом столбце вместо текста появится надпись Table и возможность развернуть данные, чтобы посмотреть, что ж там такое возвращается.
3. Скрипт, приведенный автором - это принципиальное решение, которое не обязательно подходит именно для вашего случая.
Скрипт можно и нужно допиливать под конкретный кейс. Полезно будет обернуть применение регулярного выражения в конструкцию if else.
4. В моем случае надо было вытащить айди товара (последние шесть цифр) из ссылки типа "" . Получился вот такой скрипт:
let fx=(txt as text,regex as text)=>Web.Page( "<script> var x = '" & txt & "'; var pattern = " & regex & "; if(x.match(pattern )) { document.write(x.replace(pattern ,'$2')); } else { document.write('not found'); } </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0} in fxПараметр regex в вызове функции был задан, как
/(.*)-(\d{4,6})\//g- БЕЗ КАВЫЧЕК.
С кавычками как-то упорно не складывалось ))
В результате в настраиваемом столбце у меня выводился либо айди товара, либо надпись "not found".
Спасибо еще раз Николаю за найденное и опробованное решение. У самого вендора на форуме я такого не видел.
abc,def,ghi,klmn,opqr,st,vx,y,z
подстроку
ghi
Даже когда явно указываю для символа запятой ленивую квантификацию регулярным выражением gh.*,?, работать не хочет, и чешет до самой последней запятой в строке, возвращая вот такой бред:
ghi,klmn,opqr,st,vx,y,z
Т.е. по сути находит слово, начинающееся на gh, и потом возвращает всё до конца строки. Так с таким же успехом можно использовать регулярное выражение gh.*. Но так не интересно! Мне надо не от начала подстроки и до конца, а от начала подстроки и до запятой.
Подскажите пожалуйста, как мне составить регулярное выражение для случая, когда надо вырезать фрагмент строки, начинающийся с известной последовательности символов, и заканчивающийся запятой?