Power Query Продвинутый №28. Множественная текстовая замена c List.Generate [СПОНСОРЫ]

В этом уроке мы научимся выполнять множественную текстовую замену в Power Query с помощью функции List.Generate. Для спонсоров канала я так же расскажу как это делать в VBA.

[ДмитрийНикитин-ио]

Класс! Этот способ даже более понятный, чем с List.Accumulate

[nikbo]

Тимур, супер. спасибо. пожалуйста, не пропадайте надолго! грусть нападает при отсутствии новых уроков. я к Комраду присоединился, когда еще не было тыщи человек, праздравляю уже за 2 перевалило! надеюсь к новому году цифру за 4 увидеть!!!!

[Igor_Ashurkov]

List.Generate сложно зашел с точки зрения понимания). Нужно будет "под карандаш" разобрать пример. А в целом - отличный материал. Спасибо !

[ДмитрийНикитин-ио]

Когда писал формулу сам, столкнулся с такими ошибками:
1. В Text.Replace первым аргументом сначала вписал [Компания]. Потом понял, что надо писать имя поля, заданного в initial - [txt]
2. Забывал про номер строки во втором и третьем аргументе функции Text.Replace - Замена_1[Найти] и Замена_1[Заменить]. Потом понял, что без номера строки формула работать не будет, а номер строки - переменная index.
3. Долго потом не мог понять, в чём ошибка: Замена_1[Найти]{index} и Замена_1[Заменить]{index}. Потом до меня дошло, что index - название поля в записи, значит его надо указывать в квадратных скобках {[index]}
3. В пользовательской функции имя переменной [ИсходныйТекст] писал в квадратных скобках. Потом понял, что переменная принимает на себя название поля, поэтому саму переменную нужно писать без скобок.

[Rmanovi]

Добрый день. Как делать перенос строки и табуляцию при редактировании формулы в диалоговом окне "Настраиваемый столбец"?

[skrald_]

Тимур, не могли бы Вы поделиться файлом к этому уроку? Подписка у меня есть, но она считалась спонсорской только до 15 июля 2021. А вроде у Вас при изменении условий обратной силы закон не имел)
У меня именно под этим видео ссылка отсутствует, под другими (выборочно) вижу.
Не сочтите за жадность только, я после того, как выйду на постоянный доход, обязательно подтяну донат до спонсорского уровня 😇
Спасибо за Ваши уроки, они топовые!

[zaharterentev]

Добрый день. Нужна ваша помощь. Есть подключение к папке. В ней сотни тысяч записей. Есть файлы с ошибками которые портят все работу с файлом. Как исключить строки с ошибками?

[ЮрийНиколаев-йд]

Я, наверно, правильно сделаю, если из общего места свои вопросы буду задавать здесь?
Вот начало из оттуда:
"Юрий Николаев
1. Очень долгий вариант: у меня порядка 200 000 строк в таблице, вариантов замены 21 штука. Уже работает 15 минут, а загрузилось всего 22 000 строк...
Да, забыл сказать, что использую только одну замену, а не две, как у вас. Есть вариант ускорения этой работы, или отказаться от этого преобразования в PQ и произвести это заранее в источниках?

Товарищ Excel - Power Query, Power Pivot, Python
Проверьте совпадает ли ваш код с моим. Используете ли вы Table.Buffer."

Проверка показала, что совпадение полное. Естественно, не совпадают только имена. Вот моё:
let
Источник = Table.Combine({ФЛ, ФЛ2, ЮЛ}),
#"Измененный тип" = Table.TransformColumnTypes(Источник, {{"Договор", Int64.Type}, {"Дата проверки", type date}}),
#"Добавлен пользовательский объект" = Table.AddColumn(#"Измененный тип", "Замена", each List.Last(List.Generate(
()=> [index = 0, txt = [РЭС]],
each [index] <= List.Count(Замена[Найти]),
each [
index = [index] + 1,
txt = Text.Replace([txt], Замена[Найти]{[index]},
Замена[Заменить]{[index]})
],
each [txt]
)
)),
#"Удаленные столбцы" = пользовательский объект", {"РЭС"}),
#"Переименованные столбцы" = столбцы", {{"Замена", "РЭС"}}),
#"Переупорядоченные столбцы" = столбцы", {"РЭС", "Наименование ДП", "Договор", "Наименование договора", "Дата проверки", "Причина проверки", "Типы пломб"})
in
#"Переупорядоченные столбцы"

А вот ваше:
let

source = Excel.CurrentWorkbook(){[Name="Гостиницы"]}[Content],

select_col = Table.SelectColumns(source, {"Компания"}),

col_replace_1 = Table.AddColumn(select_col,
"Замена_1",
each [index=0, txt=Text.Lower([Компания])],
each [index]<=List.Count(Замена_1[Найти]),
each [index=[index]+1,
txt=Text.Replace([txt],
Замена_1[Найти]{[index]},
Замена_1[Заменить]{[index]})],
each [txt])
))),

col_replace_2 = Table.AddColumn(col_replace_1,
"Замена_2",
each List.Last(List.Generate(()=> [index=0, txt=[Замена_1]],
each [index]<=List.Count(Замена_2[Найти]),
each [index=[index]+1,
txt=Text.Replace([txt],
Замена_2[Найти]{[index]}, Замена_2[Заменить]{[index]})],
each [txt])
))

in

col_replace_2

Я только не использую вторую замену и Text.Proper за ненадобностью.

Table.Buffer не используется, потому что ещё не знаю как и для чего его использовать. Само описание функции через #shared посмотрел.

[МайклЛе]

Проделал все операции, как в ролике. Всё получилось. Но. Понятнее не стало :( Очень удобный язык. Обучать ему можно бесконечно. На каждый чих - своя функция со своим выпендрежем. У человеков, которые его придумали, точно не все с головой в порядке.