Масова заміна тексту в Power Query за допомогою функції List.Accumulate

Як швидко і масово замінити текст за довідником формулами – ми вже розібралися. Тепер давайте спробуємо зробити це в Power Query.

Як часто буває виконувати це завдання набагато легше, ніж пояснювати чому це працює, але давайте спробуємо зробити обидва 🙂

Отже, ми маємо дві «розумні» динамічні таблиці, створені зі звичайних діапазонів за допомогою комбінації клавіш Ctrl+T або команда Домашня сторінка – Формат у вигляді таблиці (Домашня сторінка — форматувати як таблицю):

Я назвав перший столик дані, друга таблиця – Каталогвикористовуючи поле назва таблиці (Назва таблиці) таб Конструктор (Дизайн).

Завдання: замінити адреси в таблиці дані усі випадки зі стовпця Знайти Керівництво до відповідних правильних аналогів зі стовпця Замінити. Решта тексту в клітинках має залишитися недоторканою.

Крок 1. Завантажте каталог у Power Query та перетворіть його на список

Встановивши активну комірку в будь-яке місце довідкової таблиці, клацніть вкладку дані (Дата)або на вкладці PowerQuery (якщо у вас стара версія Excel і ви встановили Power Query як надбудову на окремій вкладці) на кнопці З таблиці/діапазону (З таблиці/діапазону).

Довідкову таблицю буде завантажено в редактор запитів Power Query:

Щоб не заважати, автоматично додається крок модифікований тип (Змінений тип) на правій панелі застосовані кроки можна безпечно видалити, залишивши лише крок Source (Джерело):

Тепер, щоб виконати подальші перетворення і заміни, нам потрібно перетворити цю таблицю в список (список).

Ліричний відступ

• таблиця це двовимірний масив, що складається з кількох рядків і стовпців.
• Запис (Запис) – одновимірний масив-рядок, що складається з кількох полів-елементів з іменами, напр [Ім’я = “Маша”, Стать = “ж”, Вік = 25]
  
• список – одновимірний масив-стовпець, що складається з кількох елементів, напр {1, 2, 3, 10, 42} or { "Віра, Надія, Любов" }
  

Для вирішення нашої задачі нас буде цікавити в першу чергу тип список.

Хитрість тут полягає в тому, що елементи списку в Power Query можуть бути не тільки банальними числами або текстом, а й іншими списками або записами. Саме в такому хитромудрому списку (list), що складається із записів (records), нам і потрібно звернути наш каталог. У синтаксичній нотації Power Query (записи у квадратних дужках, списки у фігурних дужках) це виглядатиме так:

{

    [ Знайти = “Св. Петербург», Замінити = «Санкт-Петербург». Петербург” ] ,

    [ Знайти = “Св. Петербург», Замінити = «Санкт-Петербург». Петербург” ] ,

    [ Знайти = “Петро”, Замінити = “Св. Петербург” ] ,

і т.п.

}

Таке перетворення виконується за допомогою спеціальної функції мови М, вбудованої в Power Query – Table.ToRecords. Щоб застосувати її безпосередньо в рядку формул, додайте цю функцію до коду кроку там Source.

Це було:

Після:

Після додавання функції Table.ToRecords зовнішній вигляд нашої таблиці зміниться – вона перетвориться на список записів. Вміст окремих записів можна побачити в нижній частині панелі перегляду, клацнувши на тлі клітинки поруч із будь-яким словом Запис (але ні одним словом!)

На додаток до вищесказаного є сенс додати ще один штрих – кешувати (буферити) наш створений список. Це змусить Power Query завантажити наш список пошуку один раз у пам’ять і не перераховувати його знову, коли ми пізніше отримаємо доступ до нього, щоб замінити його. Для цього оберніть нашу формулу в іншу функцію – Список.Буфер:

Таке кешування дасть дуже відчутний приріст швидкості (в кілька разів!) при великому обсязі вихідних даних, що підлягають очищенню.

На цьому підготовка довідника завершена.

Залишилося натиснути на Головна – Закрити та завантажити – Закрити та завантажити в… (Домашня сторінка — Close&Load — Close&Load to..)виберіть опцію Просто створіть зв'язок (тільки створити з'єднання) і повернутися до Excel.

Крок 2. Завантаження таблиці даних

Тут все банально. Як і раніше з довідником, піднімаємося в будь-яке місце таблиці, натискаємо вкладку дані button З таблиці/діапазону і наш стіл дані потрапляє в Power Query. Автоматично доданий крок модифікований тип (Змінений тип) ви також можете видалити:

Ніяких особливих підготовчих дій з ним робити не потрібно, і переходимо до найголовнішого.

Крок 3. Виконайте заміни за допомогою функції List.Accumulate

Давайте додамо обчислюваний стовпець до нашої таблиці даних за допомогою команди Додавання стовпця – спеціальний стовпець (Додати стовпець — спеціальний стовпець): і введіть назву доданого стовпця у вікні, що відкриється (наприклад, виправлена ​​адреса) і наша магічна функція Перерахувати.Накопичувати:

Залишилося натиснути на OK – і отримуємо стовпець із зробленими замінами:

Зауважте, що:

• Оскільки Power Query чутливий до регістру, заміни в передостанньому рядку не було, оскільки в каталозі ми маємо «SPb», а не «SPb».
• Якщо у вихідних даних потрібно замінити відразу кілька підрядків (наприклад, в 7 рядку потрібно замінити і «S-Pb», і «Prospectus»), то це не створює проблем (на відміну від заміни формулами з попередній спосіб).
• Якщо у вихідному тексті (9-й рядок) нічого замінити, то помилок не виникає (на відміну, знову ж таки, від заміни формулами).

Швидкість такого запиту дуже і дуже пристойна. Наприклад, для таблиці початкових даних розміром 5000 рядків цей запит оновився менше ніж за секунду (без буферизації, до речі, близько 3 секунд!)

Як працює функція List.Accumulate

В принципі, на цьому (я писав, а ви читали) цю статтю можна було б закінчити. Якщо ви хочете не тільки вміти, а й розуміти, як це працює «під капотом», то вам доведеться зануритися трохи глибше в кролячу нору і розібратися з функцією List.Accumulate, яка виконувала всю масову заміну. працювати на нас.

Синтаксис цієї функції:

=List.Accumulate(список, насіння, Акумулятор)

де

• список це список, елементи якого ми повторюємо. 
• насіння – вихідний стан
• Акумулятор – функція, яка виконує певну операцію (математичну, текстову тощо) над наступним елементом списку та накопичує результат обробки у спеціальній змінній.
  

Загалом синтаксис написання функцій у Power Query виглядає так:

(аргумент1, аргумент2, … аргументN) => деякі дії з аргументами

Наприклад, функція підсумовування може бути представлена ​​у вигляді:

(a, b) => a + b

Для List.Accumulate ця функція накопичувача має два обов’язкові аргументи (їх можна назвати як завгодно, але звичайні імена: були и ток, як в офіційній довідці для цієї функції, де:

• були – змінна, де накопичується результат (її початкове значення є згаданим вище насіння)
  
• ток – наступне повторене значення зі списку список
  

Для прикладу розглянемо кроки логіки наступної конструкції:

=List.Accumulate({3, 2, 5}, 10, (стан, поточний) => стан + поточний)

Це останні накопичені були значення є нашою функцією List.Accumulate і виводить як результат:

Якщо трохи пофантазувати, то за допомогою функції List.Accumulate можна змоделювати, наприклад, функцію Excel CONCATENATE (в Power Query її аналог називається Текст.Комбінувати) використовуючи вираз:

Або навіть шукати максимальне значення (імітація функції Excel MAX, яка в Power Query називається Список.Макс):

Однак головною особливістю List.Accumulate є можливість обробляти не лише прості текстові чи числові списки як аргументи, але й більш складні об’єкти – наприклад, списки зі списків або списки із записів (привіт, Каталог!)

Давайте знову подивимося на конструкцію, яка виконала заміну в нашій задачі:

List.Accumulate(Каталог, [адреса], (стан, поточний) => Text.Replace (стан, поточний [Знайти], поточний [Замінити]) )

Що тут насправді відбувається?

1. Як початкове значення (насіння) беремо перший незграбний текст зі стовпця [адреса] наш стіл: 199034, Санкт-Петербург, вул. Берінга, буд. 1
2. Потім List.Accumulate повторює елементи списку один за одним – Керівництво. Кожен елемент цього списку є записом, що складається з пари полів «Що знайти – Чим замінити», або, іншими словами, наступний рядок у каталозі.
3. Функція накопичувача поміщає в змінну були початкове значення (перша адреса 199034, Санкт-Петербург, вул. Берінга, буд. 1) і виконує над ним функцію накопичувача – операцію заміни за стандартною М-функцією Текст. Замінити (аналогічно функції SUBSTITUTE в Excel). Його синтаксис:

   Text.Replace(оригінальний текст, що ми шукаємо, чим ми замінюємо)

   і ось ми маємо:

• були це наша брудна адреса, яка лежить в були (дістатися туди з насіння)
  
• поточний [Пошук] – значення поля Знайти від наступного повторного запису списку Каталог, яка лежить у змінній ток
• поточний[Замінити] – значення поля Замінити від наступного повторного запису списку Каталоглежачи в ток

Таким чином, для кожної адреси щоразу виконується повний цикл перерахування всіх рядків у каталозі, замінюючи текст із поля [Знайти] на значення з поля [Замінити].

Сподіваюся, ви зрозуміли 🙂

• Масова заміна тексту в списку за допомогою формул
  
• Регулярні вирази (RegExp) у Power Query