Power Query Практический №1. Обработка типичной выписки

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

• Для спонсоров Boosty
• Для спонсоров VK

• YouTube
• VK

Ссылки:

• Страница курса
• Плейлист YouTube
• Плейлист ВК

Описание

Каждый день вам приходит выписка по эквайрингу. Она не очень удобна для дальнейшего анализа. Сперва преобразуем данные, чтобы можно было с ними работать.

Исходные данные выглядят так:

Решение

Сначала мы удалим лишние строки сверху. После этого мы создадим столбцы Адрес и POSID, данные из которых берутся из столбца N опер. Дальше останется только заполнить данные в двух новых столбцах вниз и отфильтровать строки.

Помимо пользовательского интерфейса Power Query мы воспользуемся:

• List.Select
• Text.Contains
• Text.BetweenDelimiters
• Text.AfterDelimiter
• Table.SelectRows

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

• Table.Skip
• Table.PromoteHeaders
• Table.RemoveColumns
• List.Select
• Table.ColumnNames
• Text.Contains
• Comparer.OrdinalIgnoreCase
• Table.AddColumn
• Text.StartsWith
• Text.BetweenDelimiters
• Text.Trim
• Text.AfterDelimiter
• Table.FillDown
• Table.SelectRows

Код

let
   path = Excel.CurrentWorkbook(){[Name = "Путь"]}[Content]{0}[Column1]
      & "И_Выписка_2019.01.05.xlsx", 
   source = Excel.Workbook(File.Contents(path), null, true), 
   get_table = source[Data]{0}, 
   rows_skip = Table.Skip(get_table, 5), 
   tab_headers = Table.PromoteHeaders(
      rows_skip, 
      [PromoteAllScalars = true]
   ), 
   cols_select = Table.RemoveColumns(
      tab_headers, 
      List.Select(
         Table.ColumnNames(tab_headers), 
         each Text.Contains(_, "column", Comparer.OrdinalIgnoreCase)
      )
   ), 
   tab_add_col_1 = Table.AddColumn(
      cols_select, 
      "№ терминала", 
      each try
         
            if Text.StartsWith(
               [#"№ опер."], 
               "pos", 
               Comparer.OrdinalIgnoreCase
            )
            then
               Text.BetweenDelimiters([#"№ опер."], " ", " ")
            else
               null
      otherwise
         null
   ), 
   tab_add_col_2 = Table.AddColumn(
      tab_add_col_1, 
      "Адрес", 
      each Text.Trim(
         try
            
               if Text.StartsWith(
                  [#"№ опер."], 
                  "pos", 
                  Comparer.OrdinalIgnoreCase
               )
               then
                  Text.AfterDelimiter([#"№ опер."], ",")
               else
                  null
         otherwise
            null
      )
   ), 
   cols_fill_down = Table.FillDown(tab_add_col_2, {"№ терминала", "Адрес"}), 
   rows_select = Table.SelectRows(
      cols_fill_down, 
      each ([#"Дата опер."] <> null)
   )
in
   rows_select

Этот урок входит в Практический курс Power Query

Номер урока	Урок	Описание
1	Обработка типичной выписки	В этом уроке мы обработаем типичную банковскую выписку.
2	Интересная консолидация	В этом уроке мы будем практиковать объединение таблиц по вертикали.
3	Множественная консолидация при несовпадении заголовков	Объединим таблицы по вертикали, когда заголовки таблиц не совпадают, но порядок всегда одинаков.
4	Таблица дат	Для построения отчетов в модели данных Excel вам нужно создать таблицу дат. Сейчас вы узнаете как это сделать в Power Query.
5	Продажи год назад	В этом уроке мы узнаем как в Power Query получить таблицу с продажами прошлого года напротив текущих.
6	Консолидация книг и листов одновременно	В этом уроке мы научимся объединять все листы всех книг, т. е. выполним двухуровневую консолидацию.
7	Столбец общей суммы в PQ и PP	В этом уроке мы создадим столбец, в котором будет находиться общая сумма всех строк таблицы. Так же разберем эту же операцию в Power Pivot.
8	Скученные данные 3	В этом уроке мы обработаем еще один файл со скученными данными. На этот раз в одном столбце находятся даты и номенклатура. Нужно разбить этот столбец на два.
9	Строки преобразовать в столбцы	У нас есть таблица, в которой один столбец имеет скученные данные. Нам нужно скученный столбец преобразовать в столбцы.
10	Пивот, анпивот, группировка	В этом уроке мы попрактикуем анпивот, условную логику и группировку.
11	Нужные столбцы с нужного листа	В этом уроке мы научимся извлекать нужные столбцы с нужного листа не используя название самого листа.
12	Объединение, группировка, транспонирование, анпивот	В этом уроке на очень интересном примере из реальной жизни попрактикуем несколько техник Power Query: группировка, анпивот, объединение таблиц по горизонтали, транспонирование.
13	Нарастающий итог 4, много группировок	В этом уроке мы изучим еще 1 способ создать столбец нарастающего итога в Power Query. В отличие от других способов здесь мы не будем пользоваться формулами. Все сделаем при помощи пользовательского интерфейса.
14	Количество позиций в строке	В этом уроке мы посчитаем количество наименований, перечисленных в одной ячейке.
15	List Contains, Пользовательская функция	В этом уроке повторим создание пользовательских функций и рассмотрим еще 1 пример использования функции List.Contains.
16	ABC анализ	Научимся выполнять ABC анализ в Power Query.