Массовая замена текста в 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+ " замена сработает дважды в итоге получится: Женщинам|Батал|Женщинам|Норма|Платья
19.08.2021 14:37:25
День добрый. Андрей, у вас как-то получилось решить указанную в комментарии проблему?

Т.к мне после всех манипуляций выдает эксель выдает нечто подобное, или "шт.." или "комплекткомплект" хотя по логике строки где все верно трогаться не должны были
19.08.2021 16:53:28
Здравствуйте, у меня получилось решить данную проблему. Отсортировал слова в колонке "Найти" так чтобы не было двойных совпадений. Замена работает сверху вниз, если я искал слово "Платья ХЛ+" с заменой на "Женщинам/Батал/Платье" то когда дальше по списку есть поиск слова "Женщинам" или "Батал" или "Платье", он его тоже заменит соответственно.

Если этот способ не подойдет, тогда попросту добавляем любой символ в начало или конец или и в начало и конец искомого слова (например пробел или скобку) и тогда соответственно в самом выражении тоже нужно добавить этот символ.
Искать "_Платье ХЛ+_"  добавил в начало и конец "_", тогда  и у выражение добавляем этот символ в начало и в конец:
List.Accumulate(Справочник,"_" & [Адрес] & "_", (state,current) => Text.Replace(state, current[Найти], current[Заменить]) )  
29.12.2020 11:15:46
Огромное спасибо за статью!

А можно ли перебуферизировать справочник, если такая необходимость возникнет?
16.01.2021 08:44:18
Помогло, спасибо!
S B
21.05.2021 14:17:32
Отлично, спасибо!
S B
24.05.2021 11:03:38
У меня в PQ собираются файлы и в левом столбце отображаются имена файлов, с помощью этой функции, добавил столбец и в нем заменил(подставил) имена файлов, на значения из списка "заменить"!
пример: Ромашка.xlsx  => Компания Ромашка
Вопрос, а можно ли прямо в этом столбце менять названия файлов? Или только через добавление нового столбца?
01.09.2021 12:00:08
Добрый день!

Подскажите, пожалуйста, можно ли данный способ адаптировать для замены значений в ячейке, а не части текста?

Или может уже есть урок по данному вопросу.
09.10.2022 20:38:47
У меня ошибка

Expression.Error: Не удается преобразовать значение типа Table в тип List.
Сведения:
   Value=[Table]
   Type=[Type]

Что это значит?
30.04.2023 15:18:57
Здравствуйте. Урок очень полезный. Как же все таки убрать регистро-чувствительность, чтоб все замены были произведены?
12.05.2024 10:19:13
Убрать регистрочувствительность можно, если в запросе вместо Text.Replace использовать аналогичную функцию, но которая может искать подстроку внутри текста без учета регистра. Вышеназванная встроенная к сожалению так не может, но мы можем написать свою пользовательскую функцию, которая сможет провернуть такой финт. Ниже один из возможных примеров реализации такой функции. Если при вызове данной функции в 4-м параметре указать Comparer.OrdinalIgnoreCase , то она как раз будет работать в режиме регистроНЕчувствительности; если опустить 4-й параметр при вызове, то будет работать как обычная встроенная Text.Replace (то есть будет чувствительна к регистру искомой подстроки)

// Функция Text_Replace аналогична функции Text.Replace, но поиск искомой подстроки может происходить БЕЗ учета регистра, для этого используйте 4-й параметра функции comparer
    Text_Replace = ( text as nullable text , oldSubstring as text, newSubstring as text, optional comparer as nullable function ) as nullable text =>
        let
            ReplacedRanges =
                if text = null then
                    null
                else if comparer = null or comparer = Comparer.Ordinal then
                    Text.Replace( text , oldSubstring , newSubstring )
                else
                    let
                        oldSubstringPositionList = Text.PositionOf( text , oldSubstring , Occurrence.All , comparer  ) ,
                        result =
                            if List.IsEmpty( oldSubstringPositionList ) then
                                text
                            else 
                                Text.ReplaceAllRanges( text , oldSubstringPositionList )
                    in
                        result ,
                
            Text.ReplaceAllRanges =
                ( text as nullable text , positionList as list ) as nullable text =>
                    let
                        LastPos = List.Last( positionList ) ,
                        restPos = List.FirstN( positionList , List.Count( positionList ) - 1 ) ,
                        currentText = Text.ReplaceRange( text , LastPos , Text.Length( oldSubstring ) , newSubstring ) ,
                        final =
                            if List.IsEmpty( restPos ) then
                                currentText
                           else
                                @Text.ReplaceAllRanges( currentText , restPos )
                    in
                        final
        in
            ReplacedRanges
09.07.2023 16:59:52
Евгений  что вам мешает создать столбец с данными в верхнем или нижнем регистре от исходного, средствами PQ на предыдущем шаге, и делать замены в нем, такой же шаг применить к столбцу Найти при загрузке справочника.

но тогда встанет вопрос восстановления регистра в конце )))), можно сравнить исходный и измененный столбец, и создать условный итоговый столбец с данными из исходного  либо из нового(там где данные были изменены.  
12.05.2024 10:33:50
Виктор, ваш подход не решает проблему с регистром в обработанных данных, так как в строках, где производилась замена, все символы, кроме замененных, будут в нижнем или в верхнем регистре (в зависимости от того, в каком регистре вы создавали копию исходного столбца )
02.08.2023 06:34:43
Добрый день!
А можем мы в PQ заменять массово наименования столбцов и суммировать те, что после переименования будут иметь одно и тоже название?
02.08.2023 06:37:26
Добрый день! Можем ли мы с помощью PQ  массово заменять наименования столбцов также по справочнику и затем суммировать те столбцы, которые после переименования имеют одинаковое название?
11.05.2024 12:09:15
Напрямую в лоб не можем, так как в Power Query ни до переименования столбцов, ни после имена столбцов не могут иметь одинаковые названия. Но обходными путями можем все... мммм... ну, почти все )
Наверх