зміст
Якщо ви хоч трохи знайомі з регулярними виразами, то вам не потрібно їх афішувати. Якщо ви не зовсім в темі, то регулярні вирази (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(pattern).join(';'); – виконати пошук у вихідному тексті (вул) заданим регулярним виразом (модель) і помістити результати в змінну результат, об’єднавши їх крапкою з комою за допомогою команди приєднатися
- document.write(результат); – відобразити вміст змінної результату
Також зауважте, що текстові рядки (за винятком регулярних виразів) у JavaScript узяті в апостроф, а не в лапки, як у Power Query або VBA.
На виході цей скрипт видасть нам у результаті всі числа, знайдені у вихідному тексті:
123, 789
Короткий курс JavaScript завершено, дякую всім. Сподіваюся, ви зрозуміли логіку 🙂
Залишилося перенести цю конструкцію в Power Query.
Функція пошуку та вилучення тексту за допомогою регулярного виразу в Power Query
Ми робимо наступне:
1. Відкрийте Excel і створіть новий порожній Power Query на вкладці Дані – Отримати дані / Створити запит – З інших джерел – Порожній запит (Дані — Отримати дані / Новий запит — З інших джерел — Пустий запит). Якщо у вас стара версія Excel 2010-2013 і Power Query у вас не вбудований, а встановлений як окрема надбудова, то все це буде на вкладці PowerQueryІ ні дані.
2. У порожньому вікні редактора запитів, на правій панелі відразу вводимо назву нашої майбутньої функції (наприклад, fxRegExpExtract)
3. Переходимо до вкладки Перегляд – розширений редактор (Перегляд — розширений редактор), стираємо весь М-код порожнього запиту і вставляємо туди код нашої суперфункції:
Слідкуйте за руками:
У першому рядку ми говоримо, що наша функція матиме три текстові аргументи: TXT – оригінальний текст, що аналізується, regex – шаблон регулярного виразу, розмежувати — роздільник для відображення результатів.
Далі ми викликаємо функцію Веб-сторінка, формуючи код JavaScript, описаний вище в його аргументі. Ми вставляємо та підставляємо наші змінні аргументи в код.
Фрагмент:
[Дані]{0}[Діти]{0}[Діти]{1}[Текст]{0}
… необхідний, щоб «провалитися» в таблицю з потрібними нам результатами. Справа в тому, що функція Веб-сторінка в результаті створюється кілька вкладених таблиць, які повторюють структуру веб-сторінки. Без цього фрагмента M-коду наша функція виведе таке:
… і нам довелося б клацати слово кілька разів таблиця, послідовно «провалюючись» у дочірні вкладені таблиці в стовпцях діти:
Замість всієї цієї цитати ми відразу вказуємо в коді нашої функції, яка вкладена таблиця і стовпець (текст) нам потрібно.
Ось, власне, і всі секрети. Залишилося натиснути кнопку обробка у вікні розширений редактор, куди ми вставили наш код, і можна переходити до найсмачніше – спробувати нашу функцію на роботі.
Ось кілька прикладів насіння.
Приклад 1. Отримання номера рахунку та дати з опису платежу
У нас є виписка з банку з описом (призначенням) платежів, де в окремі графи потрібно витягнути номери та дати оплачених рахунків:
Завантажуємо таблицю в Power Query стандартним способом Дані – з таблиці/діапазону (Дані — Від Тздатний/Рангел).
Потім ми додаємо обчислюваний стовпець із нашою функцією через Додати стовпець – виклик спеціальної функції (Додати стовпець — викликати спеціальну функцію) і введіть його аргументи:
Як регулярний вираз (аргумент regex) використовуємо шаблон:
(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