Power Query Power Query. Практический курс

Power Query Практический №13. Нарастающий итог 4 (Много группировок)

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

Ссылки:

Описание

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

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

Исходная таблица выглядит так:

Дата	Сумма
01.01.2020	100
01.01.2020	10
01.01.2020	150
01.02.2020	210
01.02.2020	17
01.02.2020	180
01.02.2020	46
01.02.2020	350
01.02.2020	31

Мы хотим сделать так:

Дата	Сумма	Нарастающий итог
01.01.2020	100	100
01.01.2020	10	110
01.01.2020	150	260
01.02.2020	210	210
01.02.2020	17	227
01.02.2020	180	407
01.02.2020	46	453
01.02.2020	350	803
01.02.2020	31	834
01.02.2020	600	1434
01.02.2020	57	1491
01.02.2020	400	1891
01.02.2020	81	1972
01.02.2020	160	2132
01.02.2020	96	2228

Рашение

Внутри каждый даты свой нарастающий итог. Ключом к решению данной задачи являются 2 трюка Power Query:

Столбец индекса
Группировка с типом операции «Все столбцы»

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

Table.TransformColumnTypes
Date.Type
Table.AddIndexColumn
Table.Group
Table.DuplicateColumn
Table.ExpandTableColumn
Table.SelectRows
List.Sum
Table.RemoveColumns

Код

let
   source = Excel.CurrentWorkbook(){[Name = "Таблица1"]}[Content],
   types = Table.TransformColumnTypes(
      source,
      {{"Дата", Date.Type}, {"Сумма", type number}}
   ),
   table_add_index_col = Table.AddIndexColumn(types, "Индекс", 0, 1),
   table_group = Table.Group(
      table_add_index_col,
      {"Дата"},
      {{"Все", each _, type table}}
   ),
   table_add_col_copy = Table.DuplicateColumn(
      table_group,
      "Все",
      "Все.Копия"
   ),
   col_expand = Table.ExpandTableColumn(
      table_add_col_copy,
      "Все",
      {"Сумма", "Индекс"},
      {"Сумма", "Индекс"}
   ),
   col_expand_2 = Table.ExpandTableColumn(
      col_expand,
      "Все.Копия",
      {"Сумма", "Индекс"},
      {"Все.Сумма", "Все.Индекс"}
   ),
   table_add_col_sum = Table.AddColumn(
      col_expand_2,
      "Суммировать",
      each [Индекс] >= [#"Все.Индекс"]
   ),
   rows_select = Table.SelectRows(
      table_add_col_sum,
      each ([Суммировать] = true)
   ),
   table_group_2 = Table.Group(
      rows_select,
      {"Дата", "Сумма", "Индекс"},
      {{"Нарастающий итог", each List.Sum([#"Все.Сумма"]), type number}}
   ),
   table_remove_col = Table.RemoveColumns(table_group_2, {"Индекс"})
in
   table_remove_col

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

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

Power Query. Минимум в диапазоне строк

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