Power Query Практический №12. Объединение, Группировка, Транспонирование, Анпивот

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

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

• YouTube
• VK

Ссылки:

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

Описание

В этом уроке мы разберем очень интересный пример из реальной жизни, в котором попрактикуем много техник Power Query.

Есть 2 исходные таблицы. В первой находятся ответы сотрудников на вопросы теста, а во второй правильные ответы к этому тесту.

Таблица с ответами:

Таблица правильных ответов:

Нам нужно выяснить сколько правильных ответов дал каждый сотрудник.

Решение

Очевидно, что таблицы нужно объединить, но прежде чем это сделать сначала надо их привести к нормальному виду с помощью операций Unpivot и Transpose.

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

• Excel.Workbook
• File.Contents
• Table.Transpose
• Table.SelectColumns
• Table.RenameColumns
• Table.PromoteHeaders
• Table.UnpivotOtherColumns
• Table.SplitColumn
• Splitter.SplitTextByEachDelimiter
• QuoteStyle.Csv
• Table.TransformColumnTypes
• Int64.Type
• Table.NestedJoin
• JoinKind.LeftOuter
• Table.ExpandTableColumn
• Int64.From
• Table.Group
• List.Sum
• Table.ReplaceValue
• Table.SelectRows
• Replacer.ReplaceValue
• Percentage.Type
• Table.RemoveColumns
• Table.TransformColumns
• Text.Combine
• List.Transform
• Text.From

Код

Код таблицы с правильными ответами:

let
   source = Excel.Workbook(File.Contents(path_answers), null, true),
   get_table = source{[Item = "Лист1", Kind = "Sheet"]}[Data],
   table_transpose = Table.Transpose(get_table),
   cols_select = Table.SelectColumns(
      table_transpose,
      {"Column2", "Column3"}
   ),
   cols_rename = Table.RenameColumns(
      cols_select,
      {{"Column2", "Номер"}, {"Column3", "Ответ"}}
   )
in
   cols_rename

Код итоговой таблицы:

let
   source = Excel.Workbook(File.Contents(path_test), null, true),
   get_table = source{[Item = "Ответы на форму (1)", Kind = "Sheet"]}[Data],
   table_promote_headers = Table.PromoteHeaders(
      get_table,
      [PromoteAllScalars = true]
   ),
   table_unpivot = Table.UnpivotOtherColumns(
      table_promote_headers,
      {"Отметка времени", "Ваше ФИО", " Номер Вашего ап"},
      "Атрибут",
      "Ответ"
   ),
   col_split = Table.SplitColumn(
      table_unpivot,
      "Атрибут",
      Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false),
      {"Номер", "Вопрос"}
   ),
   types = Table.TransformColumnTypes(
      col_split,
      {{"Номер", Int64.Type}},
      "en-US"
   ),
   tables_join = Table.NestedJoin(
      types,
      {"Номер"},
      right_answers,
      {"Номер"},
      "Правильные_Ответы",
      JoinKind.LeftOuter
   ),
   col_expand_table = Table.ExpandTableColumn(
      tables_join,
      "Правильные_Ответы",
      {"Ответ"},
      {"Верный ответ"}
   ),
   table_add_col = Table.AddColumn(
      col_expand_table,
      "Баллы",
      each Int64.From([Ответ] = [Верный ответ])
   ),
   table_group = Table.Group(
      table_add_col,
      {"Ваше ФИО", " Номер Вашего ап"},
      {
         {"Количество баллов", each List.Sum([Баллы]), type number},
         {
            "Таблица",
            each _,
            type table [
               Отметка времени = datetime,
               Ваше ФИО = text,
               #" Номер Вашего ап" = anynonnull,
               Номер = number,
               Вопрос = text,
               Ответ = text,
               Верный ответ = text,
               Баллы = number
            ]
         }
      }
   ),
   col_replace_value = Table.ReplaceValue(
      table_group,
      each [Таблица],
      each Table.SelectRows([Таблица], each [Баллы] = 0),
      Replacer.ReplaceValue,
      {"Таблица"}
   ),
   table_add_col_2 = Table.AddColumn(
      col_replace_value,
      "Результат",
      each [Количество баллов] / 18,
      Percentage.Type
   ),
   table_add_col_3 = Table.AddColumn(
      table_add_col_2,
      "Номер",
      each [Таблица][Номер]
   ),
   table_add_col_4 = Table.AddColumn(
      table_add_col_3,
      "Вопрос",
      each [Таблица][Вопрос]
   ),
   table_add_col_5 = Table.AddColumn(
      table_add_col_4,
      "Ответ",
      each [Таблица][Ответ]
   ),
   table_add_col_6 = Table.AddColumn(
      table_add_col_5,
      "Верный ответ",
      each [Таблица][Верный ответ]
   ),
   cols_select = Table.RemoveColumns(table_add_col_6, {"Таблица"}),
   cols_transform = Table.TransformColumns(
      cols_select,
      {
         {
            "Номер",
            each Text.Combine(List.Transform(_, Text.From), "#(lf)"),
            type text
         },
         {
            "Вопрос",
            each Text.Combine(List.Transform(_, Text.From), "#(lf)"),
            type text
         },
         {
            "Ответ",
            each Text.Combine(List.Transform(_, Text.From), "#(lf)"),
            type text
         },
         {
            "Верный ответ",
            each Text.Combine(List.Transform(_, Text.From), "#(lf)"),
            type text
         }
      }
   )
in
   cols_transform

Этот урок входит в Практический курс 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.