Массовая замена текста с помощью Power Query

Время прочтения: 2 мин.

Все мы сталкивались с отчетом где одно и то же слово может быть написано двумя, а то и более вариантами. Не всегда обычная замена («Найти и заменить» или Ctrl+H) может справиться с большим объемом и привести разношёрстный текст в единый стиль. В статье мы рассмотрим решение этой задачи с помощью функция List.Accumulate в Power Query, которая в считанные секунды выполнит преобразование текста и сделает работу с информацией намного комфортнее.

Применение функции рассмотрим на примере списка с адресами, где такие слова, как улица, город и тд. написаны разными вариантами:

А также справочник, где указаны все «неправильные» варианты и варианты, на которые необходимо будет произвести замену:

Для начала нам нужно загрузить информацию в Power Query. Первым загрузим справочник с вариантами: Выделить справочник – вкладка «Данные»- из таблицы/ диапазона.

Для использования в дальнейшем List.Accumulate, которая будет производить замену текста, необходимо справочник преобразовать в список из записей, где есть два элемента «Найти» и «Заменить».

Преобразование можно выполнить с помощью функции в Power Query – Table.ToRecords(). Данную функцию необходимо дописать в строку формул:

=Table.ToRecords(Excel.CurrentWorkbook() {[Name =»Справочник»]}[Contet])

Далее возвращаемся в Excel – вкладка «Главная» — кнопка «Закрыть и загрузить в» — в окошке «Импорт данных» опция «только создать подключение».

Вторым этапом необходимо загрузить таблицу с адресами в Power Query и выполнять в ней замену по справочнику:

Выделить таблицу адресов – вкладка «Данные»- из таблицы/ диапазона.

Теперь применим функцию List.Accumulate:

Вкладка «Добавление столбца» — Настраиваемый столбец – Настраиваемая формула столбца:

=List.Accumulate(Справочник, [Таблица 1], (state, current) =>Text.Replace(state, current[Найти], current [Заменить])).

Получаем чистый список адресов без траты времени на ручную замену текста:

Функции List.Accumulate в Power Query справляется с массовой заменой текста за несколько секунд без затрат времени и сил, которые ушли бы на ручную замену. Не стоит ограничиваться стандартными функциями Excel, когда есть множество полезных приемов, упрощающих работу.