Массовая замена текста в Power Query с помощью функции List.Accumulate

Как быстро и массово заменить текст по справочнику формулами – мы уже разобрались. Теперь попробуем сделать это в Power Query.

Как это часто бывает выполнять эта задача намного проще, чем объяснение зачем это работает, но давайте попробуем сделать и то, и другое 🙂

Итак, у нас есть две «умные» динамические таблицы, созданные из обычных диапазонов с помощью сочетания клавиш. Ctrl+T или команда Главная – Форматировать в виде таблицы (Главная страница — Форматировать как таблицу):

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

Задача: заменить адреса в таблице Данные все вхождения из столбца Найти Руководство их соответствующим правильным аналогам из столбца Замена. Остальной текст в ячейках должен оставаться нетронутым.

Шаг 1. Загрузите каталог в Power Query и превратите его в список.

Установив активную ячейку в любое место справочной таблицы, нажмите на вкладку Данные (Дата)или на вкладке Power Query (если у вас старая версия Excel и вы установили Power Query как надстройку на отдельной вкладке) на кнопку Из таблицы/диапазона (Из таблицы/диапазона).

Справочная таблица будет загружена в редактор запросов Power Query:

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

Теперь для выполнения дальнейших преобразований и замен нам необходимо превратить эту таблицу в список (список).

Лирическое отступление

• Настольные представляет собой двумерный массив, состоящий из нескольких строк и столбцов.
• Запись (Запись) – одномерный массив-строка, состоящий из нескольких полей-элементов с именами, например [Имя = «Маша», Пол = «ж», Возраст = 25]
  
• Список – одномерный массив-столбец, состоящий из нескольких элементов, например {1, 2, 3, 10, 42} or { "Вера Надежда Любовь" }
  

Для решения нашей задачи нас в первую очередь будет интересовать тип Список.

Хитрость здесь в том, что элементами списка в Power Query могут быть не только банальные числа или текст, но и другие списки или записи. Вот в такой хитрый список (список), состоящий из записей (записей), нам и нужно повернуть наш каталог. В синтаксической нотации Power Query (записи в квадратных скобках, списки в фигурных скобках) это будет выглядеть так:

{

    [Найти = «Св. Петербург», Заменить = «Св. Петербург» ] ,

    [Найти = «Св. Петербург», Заменить = «Св. Петербург» ] ,

    [ Найти = «Петр», Заменить = «Св. Петербург» ] ,

и так далее

}

Такое преобразование выполняется с помощью специальной функции языка M, встроенной в Power Query – Таблица.ToRecords. Чтобы применить ее непосредственно в строке формул, добавьте эту функцию в код шага там. Источник.

Это было:

После:

После добавления функции Table.ToRecords внешний вид нашей таблицы изменится — она превратится в список записей. Содержимое отдельных записей можно просмотреть в нижней части панели просмотра, щелкнув фон ячейки рядом с любым словом. Запись (но не одним словом!)

Помимо вышесказанного имеет смысл добавить еще один штрих — кэшировать (буферизировать) наш созданный список. Это заставит Power Query один раз загрузить наш список поиска в память и не пересчитывать его снова, когда мы позже получим к нему доступ для замены. Для этого оберните нашу формулу в другую функцию – Список.Буфер:

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

На этом подготовка справочника завершена.

Осталось нажать Главная – Закрыть и загрузить – Закрыть и загрузить в… (Главная страница — Закрыть и загрузить — Закрыть и загрузить в..), выберите вариант Просто создайте соединение (Только создать соединение) и вернитесь в Excel.

Шаг 2. Загрузка таблицы данных

Здесь все банально. Как и раньше со справочником, добираемся до любого места таблицы, нажимаем на вкладку Данные кнопка Из таблицы/диапазона и наш стол Данные попадает в Power Query. Автоматически добавленный шаг модифицированный тип (Измененный тип) вы также можете удалить:

Никаких особых подготовительных действий с ним делать не требуется, и переходим к самому главному.

Шаг 3. Выполните замены с помощью функции List.Accumulate.

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

Осталось нажать OK – и получаем столбец с сделанными заменами:

Обратите внимание, что:

• Поскольку Power Query чувствителен к регистру, замены в предпоследней строке не было, потому что в каталоге у нас стоит «SPb», а не «SPb».
• Если в исходных данных имеется сразу несколько подстрок для замены (например, в 7-й строке нужно заменить и «С-Пб», и «Проспект»), то это не создает никаких проблем (в отличие от замены формулами из предыдущий метод).
• Если в исходном тексте (9-я строка) заменять нечего, то ошибок не возникает (в отличие, опять же, от замены по формулам).

Скорость такого запроса очень и очень приличная. Например, для таблицы исходных данных размером 5000 строк этот запрос обновился менее чем за секунду (без буферизации, кстати, около 3 секунд!)

Как работает функция List.Accumulate

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

Синтаксис этой функции:

=Список.Накопить(список, семя, аккумулятор)

в котором

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

В целом синтаксис написания функций в Power Query выглядит следующим образом:

(аргумент1, аргумент2, … аргументN) => некоторые действия с аргументами

Например, функцию суммирования можно представить как:

(а, б) => а + б

Для List.Accumulate эта аккумуляторная функция имеет два обязательных аргумента (их можно назвать как угодно, но обычные имена — состояние и текущий, как в официальной справке по этой функции, где:

• состояние — переменная, в которой накапливается результат (ее начальное значение — указанное выше семя)
  
• текущий – следующее повторяемое значение из списка список
  

Для примера рассмотрим этапы логики следующей конструкции:

=Список.Накопить({3, 2, 5}, 10, (состояние, текущий) => состояние + текущий)

Это последнее накопленное состояние значением является наша функция List.Accumulate и выводит результат:

Если немного пофантазировать, то с помощью функции List.Accumulate можно смоделировать, например, функцию Excel СЦЕПИТЬ (в Power Query ее аналог называется Текст.Объединить), используя выражение:

Или даже искать максимальное значение (имитация функции MAX в Excel, которая в Power Query называется Список.Макс):

Однако главная особенность List.Accumulate — это возможность обрабатывать в качестве аргументов не только простые текстовые или числовые списки, но и более сложные объекты — например, списки-из-списков или списки-из-записей (привет, Каталог!)

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

Список.Накопить(Каталог, [Адрес], (состояние,текущий) => Text.Replace(состояние, текущий[Найти], текущий[Заменить]) )

Что на самом деле здесь происходит?

1. В качестве начального значения (семя) берем первый корявый текст из колонки [Адрес] наш стол: 199034, г. Санкт-Петербург, ул. Беринга, д. 1
2. Затем List.Accumulate перебирает элементы списка один за другим: Руководство. Каждый элемент этого списка представляет собой запись, состоящую из пары полей «Что найти – Чем заменить» или, другими словами, следующей строки в справочнике.
3. Функция аккумулятора помещает в переменную состояние начальное значение (первый адрес 199034, г. Санкт-Петербург, ул. Беринга, д. 1) и выполняет над ним функцию аккумулятора – операцию замены с помощью стандартной М-функции Текст.Заменить (аналог функции ЗАМЕНИТЬ в Excel). Его синтаксис:

   Text.Replace(исходный текст, что ищем, чем заменяем)

   и вот у нас есть:

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

Таким образом, для каждого адреса каждый раз выполняется полный цикл перебора всех строк в справочнике, заменяя текст из поля [Найти] значением из поля [Заменить].

Надеюсь, вы поняли идею 🙂

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