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

20588 25.08.2020 Скачать пример

Как быстро и оптом заменять текст по списку-справочнику формулами - мы уже разбирали. Теперь давайте попробуем сделать это в 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 дал прирост производительности, ибо на ТечНете меня упорно убеждали, что буферизуются только значения простых типов, т.е. если вы кладете в буфер список из чисел/текста, то оно запомнится, а те элементы которые представляют из себя списки/записи/таблицы, все равно будут рассчитываться лениво, т.е. только в момент обращения к ним. А у вас список из записей.
Ссылка
belsergey
28.08.2020 14:13:33
Спасибо! Отличное решение.
Ссылка
Сергей Шишкин
07.09.2020 16:36:21
Все отлично, но как подставлять замену вне зависимости от регистра, в котором набран исходный текст? Может использовать текстовую функцию Text.Proper?
Ссылка
Oksana
22.09.2020 16:08:10
Спасибо за статью! А почему обошли вниманием полную замену текста - аналог Случая 1 в массовой замене формулами (отсюда www.planetaexcel.ru/techniques/7/13714/ )? Для подобной замены есть решение в PQ?
Ссылка
TankSpb
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+ " замена сработает дважды в итоге получится: Женщинам|Батал|Женщинам|Норма|Платья
Ссылка
Moira
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
Огромное спасибо за статью!

А можно ли перебуферизировать справочник, если такая необходимость возникнет?
Ссылка
Julia Titarenko
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
Добрый день!

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

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

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

Что это значит?
Ссылка
Евгений Махонин
30.04.2023 15:18:57
Здравствуйте. Урок очень полезный. Как же все таки убрать регистро-чувствительность, чтоб все замены были произведены?
Ссылка
neophyte neophyte
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 на предыдущем шаге, и делать замены в нем, такой же шаг применить к столбцу Найти при загрузке справочника.

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