Массовая замена текста в 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 дал прирост производительности, ибо на ТечНете меня упорно убеждали, что буферизуются только значения простых типов, т.е. если вы кладете в буфер список из чисел/текста, то оно запомнится, а те элементы которые представляют из себя списки/записи/таблицы, все равно будут рассчитываться лениво, т.е. только в момент обращения к ним. А у вас список из записей.
P.S. Вопрос решился добавлением замены null на пусто в справочнике, перед преобразованием таблицы в список
= Table.ReplaceValue(Источник,null,"",Replacer.ReplaceValue,{"Заменить"})Вообще, круто!
Просьба, побольше роликов по Power Query.
Спасибо.
List.Accumulate({0..Table.RowCount(Справочник)-1}, [Адрес],
(state, current) => Text.Replace(state, Справочник[Найти]{current}, Справочник[Заменить]{current}))
Почему заменяет дважды если например такая ситуация:
Найти = "Платья XL+", Заменить = "Женщинам|Батал|Платья"
Найти = "Платья", Заменить = "Женщинам|Норма|Платья"
В таком случаи при поиске " Платья XL+ " замена сработает дважды в итоге получится: Женщинам|Батал|Женщинам|Норма|Платья
Т.к мне после всех манипуляций выдает эксель выдает нечто подобное, или "шт.." или "комплекткомплект" хотя по логике строки где все верно трогаться не должны были
Если этот способ не подойдет, тогда попросту добавляем любой символ в начало или конец или и в начало и конец искомого слова (например пробел или скобку) и тогда соответственно в самом выражении тоже нужно добавить этот символ.
Искать "_Платье ХЛ+_" добавил в начало и конец "_", тогда и у выражение добавляем этот символ в начало и в конец:
А можно ли перебуферизировать справочник, если такая необходимость возникнет?
пример: Ромашка.xlsx => Компания Ромашка
Вопрос, а можно ли прямо в этом столбце менять названия файлов? Или только через добавление нового столбца?
Подскажите, пожалуйста, можно ли данный способ адаптировать для замены значений в ячейке, а не части текста?
Или может уже есть урок по данному вопросу.