Массовая замена текста в 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).
Лирическое отступление
- Таблица (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:
Обратите внимание, что:
- Поскольку Power Query является регистрочувствительным, то в предпоследней строчке замена не произошла, т.к. в справочнике у нас "СПб", а не "Спб".
- Если в исходных данных встречаются сразу несколько подстрок для замены (например, в 7-й строке нужно заменять и "С-Пб" и "проспект"), то это никаких проблем не создает (в отличие от замены формулами из предыдущего способа).
- Если в исходном тексте нечего заменять (9-я строка), то никаких ошибок не возникает (в отличие, опять же, от замены формулами).
Скорость выполнения такого запроса - очень и очень приличная. Например, для таблицы исходных данных размером в 5000 строк этот запрос обновлялся меньше секунды (без буферизации, кстати, около 3 сек!)
Как работает функция List.Accumulate
В принципе, на этом можно было бы и закончить (мне - писать, а вам - читать) эту статью. Если же хочется не просто уметь, а ещё и понимать как это работает "под капотом", то придется нырнуть чуть глубже в кроличью нору и разобраться с функцией List.Accumulate, которая и выполнила у нас всю работу по массовой замене.
Синтаксис этой функции таков:
=List.Accumulate(list, seed, accumulator)
где
- list - список, элементы которого мы перебираем.
- seed - начальное состояние
- accumulator - функция, выполняющая какую-либо операцию (математическую, текстовую и т.д.) над очередным элементом списка и накапливающая результат обработки в специальной переменной.
(аргумент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)
- Значение переменной state задаётся равным начальному - аргументу seed, т.е. state = 10
- Берем первый элемент списка (current = 3) и плюсуем его к переменной state (10). Получаем state = 13.
- Берем второй элемент списка (current = 2) и плюсуем его текущему накопленному значению в переменной state (13). Получаем state = 15.
- Берем третий элемент списка (current = 5) и плюсуем его текущему накопленному значению в переменной state (15). Получаем state = 20.
Это последнее накопившееся в state значение наша функция List.Accumulate и выводит как результат:
Если чуть пофантазировать, то с помощью функции List.Accumulate можно сымитировать, например, Excel'евскую функцию СЦЕПИТЬ (в Power Query её аналог называется Text.Combine) с помощью выражения:
Или даже выполнить поиск максимального значения (имитация Excel'евской функций МАКС, которая в Power Query называется List.Max):
Однако, главная фишка List.Accumulate - это возможность в качестве аргументов обрабатывать не только простые текстовые или числовые списка, а более сложные объекты - например, списки-из-списков или списки-из-записей (привет, Справочник!)
Давайте-ка ещё раз посмотрим на конструкцию, которая выполняла замену в нашей задаче:
List.Accumulate(Справочник, [Адрес], (state,current) => Text.Replace(state, current[Найти], current[Заменить]) )
Что тут на самом деле происходит?
- В качестве начального значения (seed) мы берём первый корявый текст из столбца [Адрес] нашей таблицы: 199034, С-Петербург, ул. Беринга, д. 1
- Затем List.Accumulate поочерёдно перебирает элементы списка - Справочника. Каждый элемент этого списка - это запись, состоящая из пары полей "Что найти - На что заменить" или, другими словами, очередная строка в справочнике.
- Функция-аккумулятор помещает в переменную state начальное значение (первый адрес 199034, С-Петербург, ул. Беринга, д. 1) и выполняет над ним функцию-аккумулятор - операцию замены с помощью стандартной М-функции Text.Replace (аналог Excel'евской функции ПОДСТАВИТЬ). Её синтаксис:
Text.Replace( исходный текст, что ищем, на что заменяем )
и здесь у нас:
- state - это наш грязный адрес, который лежит в state (попав туда из seed)
- current[Найти] - значение поля Найти из очередной перебираемой записи списка Справочник, лежащей в переменной current
- current[Заменить] - значение поля Заменить из очередной перебираемой записи списка Справочник, лежащей в current
Таким образом для каждого адреса каждый раз прогоняется полный цикл перебора всех строк в справочнике с заменой текста из поля [Найти] на значение из поля [Заменить].
Надеюсь, вы поймали идею :)
Ссылки по теме
На таком примере собрать в кучу и Accumulate и Buffer и про составные типы еще просветить. Дорогого стоит.
Кстати, странно что List.Buffer дал прирост производительности, ибо на ТечНете меня упорно убеждали, что буферизуются только значения простых типов, т.е. если вы кладете в буфер список из чисел/текста, то оно запомнится, а те элементы которые представляют из себя списки/записи/таблицы, все равно будут рассчитываться лениво, т.е. только в момент обращения к ним. А у вас список из записей.