Power Query. Основы. Скученные данные

Описание

В этом уроке мы научимся преобразовывать данные, которые все находятся в одном столбце. Бывают разные программные продукты, в которых выгрузка происходит одним столбцом. Вам потом нужно как-то этот столбец преобразовать в нормальную таблицу.

Решение

Если присмотреться, то мы увидим, что данные по каждому тикеру длятся 9 строк.

Мы создадим столбец индекса, а потом создадим столбец с вычислением остатка от деления на 9. В результате начало каждой строки будет обозначено цифрой 0, а конец цифрой 0.

Чтобы преобразовать этот столбец в нормальную таблицу мы применим следующие приемы:

• Столбец индекса
• Остаток от деления
• Столбец сведения (Пивот)
• Заполнение вниз

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

• Table.AddIndexColumn
• Table.AddColumn
• Number.Mod
• Table.Pivot
• Table.TransformColumnTypes
• List.Distinct
• Table.FillUp
• Table.SelectRows
• Table.SelectColumns
• Table.RenameColumns

Код

let
   source = Excel.CurrentWorkbook(){[Name = "Сделки"]}[Content], 
   tab_index_col = Table.AddIndexColumn(source, "Индекс", 0, 1), 
   tab_mod_col = Table.AddColumn(
      tab_index_col, 
      "Остаток от деления", 
      each Number.Mod([Индекс], 9), 
      type number
   ), 
   tab_pivot = Table.Pivot(
      Table.TransformColumnTypes(
         tab_mod_col, 
         {{"Остаток от деления", type text}}, 
         "en-US"
      ), 
      List.Distinct(
         Table.TransformColumnTypes(
            tab_mod_col, 
            {{"Остаток от деления", type text}}, 
            "en-US"
         )[#"Остаток от деления"]
      ), 
      "Остаток от деления", 
      "Сделки"
   ), 
   cols_fillup = Table.FillUp(tab_pivot, {"1", "2", "3", "4", "5"}), 
   rows_select = Table.SelectRows(cols_fillup, each ([0] <> null)), 
   cols_select = Table.SelectColumns(
      rows_select, 
      {"0", "1", "2", "3", "4", "5"}
   ), 
   cols_rename = Table.RenameColumns(
      cols_select, 
      {
         {"0", "Дата"}, 
         {"1", "Тикер"}, 
         {"2", "Сумма"}, 
         {"3", "Количество"}, 
         {"4", "Цена"}, 
         {"5", "Комиссия"}
      }
   ), 
   types = Table.TransformColumnTypes(
      cols_rename, 
      {
         {"Дата", type date}, 
         {"Тикер", type text}, 
         {"Сумма", type number}, 
         {"Количество", type number}, 
         {"Цена", type number}, 
         {"Комиссия", type number}
      }
   )
in
   types