Power Query Power Query. Продвинутый курс

Power Query Продвинутый №26. Множественная замена текста c List.Accumulate и VBA

Файлы к уроку:

Ссылки:

Описание

В этом уроке вы узнаете как выполнить множественную замену текста в Power Query.

В наших данных есть такой столбец:

Компания

Открытое акционерное общество Санаторий "Красная Талка"

Открытое акционерное общество фирма "Геленджик-Отель"

Закрытое акционерное общество «НЕВА ИНТЕРНЕЙШЕНЕЛ»

Закрытое акционерное общество пансионат "Приветливый берег"

Закрытое акционерное общество лечебно-оздоровительный комплекс "Солнечная"

Закрытое акционерное общество "Санаторий "Ейск"

Закрытое акционерное общество "Санаторий "Предгорье Кавказа"

Нам нужно сделать такие замены:

Найти	Заменить
открытое акционерное общество	ОАО
закрытое акционерное общество	ЗАО
индивидуальный предприниматель	ИП
общество с ограниченной ответственностью	ООО
"	«
«	«
»	«
&	&
акционерное общество	АО
образовательный фонд	ОФ

Решение

Для решения понадобится создать 2 таблицы: таблицу замен и таблицу изменения propercase на uppercase.

Найти	Заменить
Оао	ОАО
Зао	ЗАО
Ип	ИП
Ооо	ООО
Ао	АО
Нао	НАО
Оф	ОФ

Примененные функции

Table.Buffer
Table.SelectColumns
Table.AddColumn
List.Accumulate
List.Positions
Text.Lower
Text.Proper
Table.TransformColumns
Table.ReplaceValue
Text.Replace
Replacer.ReplaceText

Код

Первая таблица замен:

let
   source = Table.Buffer(
      Excel.CurrentWorkbook(){[Name = "Замена_1"]}[Content]
   )
in
   source

Вторая таблица замен:

let
   source = Table.Buffer(
      Excel.CurrentWorkbook(){[Name = "Замена_2"]}[Content]
   )
in
   source

Выполняем замену:

let
   source = Excel.CurrentWorkbook(){[Name = "Гостиницы"]}[Content],
   cols_select = Table.SelectColumns(source, {"Компания"}),
   col_custom = Table.AddColumn(
      cols_select,
      "Пользовательская",
      each List.Accumulate(
         List.Positions(Замена_1[Найти]),
         Text.Lower([Компания]),
         (state, current) =>
            Text.Replace(
               state,
               Замена_1[Найти]{current},
               Замена_1[Заменить]{current}
            )
      )
   ),
   col_transform = Table.TransformColumns(
      col_custom,
      {{"Пользовательская", Text.Proper, type text}}
   ),
   col_replace_value = Table.ReplaceValue(
      col_transform,
      each [Пользовательская],
      each List.Accumulate(
         List.Positions(Замена_2[Найти]),
         [Пользовательская],
         (state, current) =>
            Text.Replace(
               state,
               Замена_2[Найти]{current},
               Замена_2[Заменить]{current}
            )
      ),
      Replacer.ReplaceText,
      {"Пользовательская"}
   )
in
   col_replace_value

Этот урок входит в Продвинутый курс Power Query

Номер урока	Урок	Описание
1	Power Query Продвинутый №1. Введение в Язык М 1	В этом уроке мы узнаем, почему стоит изучать формулы М в Power Query. Какие преимущества нам дает знание языка формул М.
2	Power Query Продвинутый №2. Введение в Язык М 2	В этом втором вводном уроке по языку формул Power Query мы познакомимся с самыми основами языка форму М.
3	Power Query Продвинутый №3. Уровни владения Языком М	В этом уроке мы узнаем зачем изучать Power Query. Какой процент задач вы сможете решить только с пользовательским интерфейсом, а какой процент вы сможете решить с использованием функций М? Об этом узнаем в это видео.
4	Power Query Продвинутый №4. Объекты Power Query	В этом уроке мы познакомимся с объектами Power Query: Table, List, Record.
5	Power Query Продвинутый №5. Объект List 1	В этом уроке мы начнем знакомиться с объектом List в Excel Power Query. Создадим простые List (списки) из чисел и букв. Познакомимся с функциями для создания листов/списков из чисел и дат. Научимся создавать список названий месяцев. Изучим функцию List.FindText. Создадим лист с условием при помощи List.Transform. Найдем среднее значение в листе/списке при помощи простой функции List.Average.
6	Power Query Продвинутый №6. Объект List 2	В этом уроке мы продолжим знакомиться с объектом List. Мы узнаем как создавать листы, как ссылаться на элементы листа и изучим несколько полезных формул листа.
7	Power Query Продвинутый №7. Объект Record (Запись)	В этом уроке немного подробнее изучим объект Record в Power Query. Record — это набор пар ключ-значение. Каждый Record — это строка таблицы. В этом уроке мы изучим ссылки на Record и функции Record.
8	Power Query Продвинутый №8. Объект Table (Таблица)	В этом уроке мы познакомимся с объектом Table в Power Query. Что это такое и какие основные функции применяются к этому объекту.
9	Power Query Продвинутый №9. Ссылки 1	В этом уроке из курса по Power Query мы повторим как ссылаться на строки таблицы.
10	Power Query Продвинутый №10. Ссылки 2	В этом уроке из курса обучения Power Query будем практиковать ссылки. Помимо ссылок еще повторим и изучим несколько техник и команд: объединение листов по вертикали, Table.Skip, Table.PromoteHeaders.
11	Power Query Продвинутый №11. Ссылки 3	В этом уроке курса по Power Query мы повторим ссылки на запись, ссылки на значение, ссылки на другую строку. Также вспомним как создавать таблицу параметров.
12	Power Query Продвинутый №12. Ссылки 4: Ссылка на другой шаг запроса и много практики	В этом уроке мы повторим/научимся ссылаться на ячейку, которая находится в другом шаге запроса.
13	Power Query Продвинутый №13. Пользовательские функции	В этом уроке мы научимся создавать пользовательские функции. Разберем пользовательские функции на примере столбца с нарастающим итогом.
14	Power Query Продвинутый №14. #datetime, #duration: работа с датой и временем	В этом уроке по Excel Power Query вы научитесь пользоваться функциями #duration и #datetime для создания даты/времени и длительности. Изучите функцию DateTimeZone.UtcNow() Узнаете как складывать данные в формате datetime и duration. Познакомитесь с функцией DateTime.LocalNow(). Научитесь преобразовывать дату/время в текст при помощи DateTime.ToText()
15	Power Query Продвинутый №15. Текстовые функции / Функции Text.*	В этом уроке по формулам Power Query мы разберем функции группировки, сведения, отмены сведения, сортировки и фильтра.

Номер урока	Урок	Описание
16	Power Query Продвинутый №16. Важные операции: Группировка, Пивот, Анпивот, Сортировка	В этом уроке по формулам Power Query мы разберем функции группировки, сведения, отмены сведения, сортировки и фильтра.
17	Power Query Продвинутый №17. Нюансы консолидации из разных книг/листов Excel и List PositionOf	В этом уроке мы узнаем о некоторых нюансах объединения таблиц по вертикали из разных файлов.
18	Power Query Продвинутый №18. Разделить столбец на строки, Text.Trim	В этом уроке мы разделим текстовый столбец на строки. Данные скучены в одной ячейке. Нужно разбить ее на несколько.
19	Power Query Продвинутый №19. Заголовки в двух строках, List.Zip	В этом уроке мы обработаем заголовки, которые находятся в двух строках. Нам понадобится функция List.Zip
20	Power Query Продвинутый №20. Вычисления в Power Query	В этом уроке мы научимся выполнять вычисления в Power Query и в Power Pivot.
21	Power Query Продвинутый №21. Скользящее среднее, List Range	В этом уроке мы научимся считать скользящее среднее в Power Query. Для примера рассчитаем скользящее среднее предыдущих 13 значений. Нам пригодятся функции List.Range и List.Average.
22	Power Query Продвинутый №22. Агрегирование текста, группировка	В этом уроке вы научитесь выполнять группировку с агрегированием текстовых значений в Power Query.
23	Power Query Продвинутый №23. Трансформация столбцов, Table.TransformColumns	В этом уроке мы изучим функцию Table.TransformColumns.
24	Power Query Продвинутый №24. Разгруппировка, Скрытые возможности Table.ReplaceValue	В этом уроке мы научимся делать разгруппировку данных в Power Query, а также изучим скрытые возможности функции Table.ReplaceValue.
25	Power Query Продвинутый №25. Функция List.Accumulate	В этом уроке мы с нуля разберем функцию List.Accumulate. Сначала я объясню цикл For, а потом мы закрепим знания на несложных, но очень интересных примерах.
26	Power Query Продвинутый №26. Множественная замена текста c List.Accumulate и VBA	В этом уроке мы научимся делать множественную текстовую замену с помощью Power Query. Для этого мы воспользуемся функцией List.Accumulate. Также разберем как решить эту задачу при помощи VBA.
27	Power Query Продвинутый №27. Генерируем список URL (Text.Format, List.Generate, List.Accumulate)	В этом уроке мы сгенерируем список веб-страниц, в которых будет меняться параметр id. Сделаем это всего лишь в одном шаге при помощи функций Text.Format, List.Generate и List.Accumulate.
28	Power Query Продвинутый №28. Множественная текстовая замена c List.Generate	В этом уроке мы научимся выполнять множественную текстовую замену в Power Query с помощью функции List.Generate. Для спонсоров канала я так же расскажу как это делать в VBA.
29	Power Query Продвинутый №29. Генераций таблиц с List.Generate, График платежей по кредиту	Продолжим изучать функцию List.Generate. Создадим при помощи этой функции целую таблицу, которая будет представлять из себя график платежей по кредиту.
30	Power Query Продвинутый №30. Нарастающий итог с List.Generate	В этом уроке мы разберем функцию List.Generate, а также при помощи этой функции выполним нарастающий итог, который в сотни раз быстрее предыдущих версий нарастающего итога.

предыдущая запись

Power Query Продвинутый №27. Генерируем список URL (Text.Format, List.Generate, List.Accumulate)

следующая запись