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

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

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

Итак, имеем две "умных" динамических таблицы, созданных из обычных диапазонов сочетанием клавиш Ctrl+T или командой Главная - Форматировать как таблицу (Home - Format as Table):

Исходные таблицы

Первую таблицу я назвал Данные, вторую таблицу - Справочник, используя поле Имя таблицы (Table name) на вкладке Конструктор (Design).

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

Шаг 1. Грузим справочник в Power Query и превращаем в список

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

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

Загруженный справочник

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

Удаляем лишний шаг определения типов

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

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

Прежде чем продолжать, давайте сначала разберёмся с терминами. Power Query умеет работать с несколькими типами объектов:

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

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

{

    [ Найти = "СПб", Заменить = "Санкт-Петербург" ] ,

    [ Найти = "С-Пб", Заменить = "Санкт-Петербург" ] ,

    [ Найти = "Питер", Заменить = "Санкт-Петербург" ] ,

и т.д.

}

Выполняется такое преобразование при помощи специальной функции встроенного в Power Query языка М - Table.ToRecords. Чтобы её применить прямо в строке формул дописываем эту функцию к имеющемуся там коду шага Источник.

Было:

Было

Стало:

Стало

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

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

Добавляем буферизацию

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

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

Осталось нажать на Главной - Закрыть и загрузить - Закрыть и загрузить в... (Home - Close&Load - Close&Load to..), выбрать вариант Только создать подключение (Only create connection) и вернуться в Excel.

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

Тут всё банально. Как и ранее со справочником, встаём в любое место таблицы, жмем на вкладке Данные (Data) кнопку Из таблицы / диапазона (From Table/Range) и наша таблица Данные попадает в Power Query. Автоматически добавленный шаг Измененный тип (Changed Type) также можно убрать:

Таблица данных

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

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

Добавим к нашей таблице данных вычисляемый столбец с помощью команды Добавление столбца - Настраиваемый столбец (Add column - Custom column): и введём в открывшееся окно имя добавляемого столбца (например, Исправленный адрес) и нашу волшебную функцию List.Accumulate:

Добавляем столбец с функцией List.Accumulate

Останется нажать на ОК - и мы получаем колонку с выполненными заменами:

Результаты

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

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

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

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

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

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

=List.Accumulate(list, seed, accumulator)

где

  • list - список, элементы которого мы перебираем. 
  • seed - начальное состояние
  • accumulator - функция, выполняющая какую-либо операцию (математическую, текстовую и т.д.) над очередным элементом списка и накапливающая результат обработки в специальной переменной.
В общем случае, синтаксис записи функций в Power Query выглядит так:

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

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

(a, b) => a + b

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

  • state - переменная, где накапливается результат (её исходное значение - это и есть упомянутый выше seed)
  • current - очередное перебираемое значение из списка list

Например, давайте разберём по шагам логику работы вот такой конструкции:

=List.Accumulate({3, 2, 5}, 10, (state, current) => state + current)

  1. Значение переменной state задаётся равным начальному - аргументу seed, т.е. state = 10
  2. Берем первый элемент списка (current = 3) и плюсуем его к переменной state (10). Получаем state = 13.
  3. Берем второй элемент списка (current = 2) и плюсуем его текущему накопленному значению в переменной state (13). Получаем state = 15.
  4. Берем третий элемент списка (current = 5) и плюсуем его текущему накопленному значению в переменной state (15). Получаем state = 20.

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

Пример на List.Accumulate

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

Имитация Text.Combine с помощью List.Accumulate

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

Имитация МАКС

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

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

List.Accumulate(Справочник, [Адрес], (state,current) => Text.Replace(state, current[Найти], current[Заменить]) )

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

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

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

    и здесь у нас:

    • state - это наш грязный адрес, который лежит в state (попав туда из seed)
    • current[Найти] - значение поля Найти из очередной перебираемой записи списка Справочник, лежащей в переменной current
    • current[Заменить] - значение поля Заменить из очередной перебираемой записи списка Справочник, лежащей в current

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

Надеюсь, вы поймали идею :)

Ссылки по теме




26.08.2020 11:18:17
Николай, вы как всегда на высоте!
На таком примере собрать в кучу и Accumulate и Buffer и про составные типы еще просветить. Дорогого стоит.
Кстати, странно что List.Buffer дал прирост производительности, ибо на ТечНете меня упорно убеждали, что буферизуются только значения простых типов, т.е. если вы кладете в буфер список из чисел/текста, то оно запомнится, а те элементы которые представляют из себя списки/записи/таблицы, все равно будут рассчитываться лениво, т.е. только в момент обращения к ним. А у вас список из записей.
28.08.2020 14:13:33
Спасибо! Отличное решение.
07.09.2020 16:36:21
Все отлично, но как подставлять замену вне зависимости от регистра, в котором набран исходный текст? Может использовать текстовую функцию Text.Proper?
22.09.2020 16:08:10
Спасибо за статью! А почему обошли вниманием полную замену текста - аналог Случая 1 в массовой замене формулами (отсюда www.planetaexcel.ru/techniques/7/13714/ )? Для подобной замены есть решение в PQ?
30.09.2020 21:06:54
Спасибо! Только очень часто требуется замена на  пустое значение (null) при этом функция выдает ошибку: Не удается преобразовать значение null в тип Text. Можно это как-то исправить?

P.S. Вопрос решился добавлением замены null на пусто в справочнике, перед преобразованием таблицы в список
= Table.ReplaceValue(Источник,null,"",Replacer.ReplaceValue,{"Заменить"})
08.11.2020 07:23:00
Здравствуйте!
Вообще, круто!
Просьба, побольше роликов по Power Query.
Спасибо.
15.11.2020 17:07:55
Спасибо. Натолкнули на хорошую идею: Справочник превратить в список чисел и затем делать замену. Выглядит так:

List.Accumulate({0..Table.RowCount(Справочник)-1}, [Адрес],
(state, current) => Text.Replace(state, Справочник[Найти]{current}, Справочник[Заменить]{current}))
20.11.2020 00:59:04
Здравствуйте!
Почему заменяет дважды если например такая ситуация:

Найти = "Платья XL+", Заменить = "Женщинам|Батал|Платья"
Найти = "Платья", Заменить = "Женщинам|Норма|Платья"

В таком случаи при поиске " Платья XL+ " замена сработает дважды в итоге получится: Женщинам|Батал|Женщинам|Норма|Платья
Наверх