Power Query Практический №10. Пивот, Анпивот, Группировка

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

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

• YouTube
• VK

Ссылки:

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

Описание

В этом уроке мы решим одну реальную практическую задачу, в которой повторим группировку, пивот, анпивот и условный столбец.

Наши исходные данные выглядят следующим образом:

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

В результате мы должны получить следующую таблицу:

Решение

Сначала мы выполним операцию Анпивот, чтобы превратить длинную горизонтальную таблицу в нормальную вертикальную.

После этого мы создадим условный столбец, в котором мы проверяем выполнен ли план за каждый конкретный день.

Далее следует операция группировки, где мы посчитаем количество выполненных и невыполненных дней.

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

• Excel.Workbook
• File.Contents
• Table.PromoteHeaders
• Table.UnpivotOtherColumns
• Table.TransformColumnTypes
• Int64.Type
• Table.RemoveRowsWithErrors
• Table.AddColumn
• Table.Group
• Table.RowCount
• Table.Pivot
• List.Distinct
• List.Sum

Код

let
   source = Excel.Workbook(File.Contents(Путь)),
   get_table = source{[Item = "План_факт сентябрь", Kind = "Sheet"]}[Data],
   table_promote_headers = Table.PromoteHeaders(
      get_table,
      [PromoteAllScalars = true]
   ),
   table_unpivot = Table.UnpivotOtherColumns(
      table_promote_headers,
      {"Дилер", "ТТ", "План"},
      "Месяц",
      "Результат"
   ),
   types = Table.TransformColumnTypes(
      table_unpivot,
      {
         {"Месяц", type date},
         {"План", Int64.Type},
         {"Результат", Int64.Type}
      }
   ),
   rows_remove_errs = Table.RemoveRowsWithErrors(types, {"Месяц"}),
   table_add_col = Table.AddColumn(
      rows_remove_errs,
      "Выполнено/Не выполнено",
      each if [Результат] < [План] then "Не выполнено" else "Выполнено"
   ),
   table_group = Table.Group(
      table_add_col,
      {"Дилер", "ТТ", "Выполнено/Не выполнено"},
      {{"Количество", each Table.RowCount(_), type number}}
   ),
   table_pivot = Table.Pivot(
      table_group,
      List.Distinct(table_group[#"Выполнено/Не выполнено"]),
      "Выполнено/Не выполнено",
      "Количество",
      List.Sum
   ),
   table_add_col_2 = Table.AddColumn(
      table_pivot,
      "Штраф",
      each if [Не выполнено] = null then null else "Штраф"
   )
in
   table_add_col_2

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