Power Query. Формулы М. Вычисления в Power Query

---

Описание

В этом уроке мы выполним 4 задачи на вычисления в Power Query и Power Pivot:

1. Процент от общей суммы по столбцу
2. Процентное изменение
3. Ранг
4. Количество уникальных значений

Решение

Процент от общей суммы

Чтобы найти процент от общей суммы сначала создадим столбец, в котором в каждой строке будет находиться общая сумма.

Далее останется только разделить значение в каждой строке на значение в созданном столбце.

Изменение

Чтобы вычислить разницу между значением в текущей строке на значение в предыдущей надо сначала создать столбец индекса.

Именно значение из столбца индекса позволяет нам ссылаться на любую строку таблицы.

Ранг

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

Уникальные значения

Для вычисления уникального значения мы воспользуемся операцией группировки с функцией List.Distinct.

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

• Table.TransformColumnTypes
• Table.AddColumn
• List.Sum
• Number.Type
• Percentage.Type
• Int64.Type
• Table.AddIndexColumn
• List.Count
• List.Select
• Table.Sort
• Order.Ascending
• Table.RemoveColumns
• Table.Group
• Table.RowCount
• Table.Distinct

Код

Процент от общей суммы:

let
   source = Excel.CurrentWorkbook(){[Name = "Таблица1"]}[Content],
   types = Table.TransformColumnTypes(
      source,
      {{"Категория", type text}, {"Сумма по категории", type number}}
   ),
   table_add_col_1 = Table.AddColumn(
      types,
      "Общая сумма",
      each List.Sum(types[Сумма по категории]),
      Number.Type
   ),
   table_add_col_2 = Table.AddColumn(
      table_add_col_1,
      "Процент от общей суммы",
      each [Сумма по категории] / [Общая сумма],
      Percentage.Type
   )
in
   table_add_col_2

Изменение:

let
   source = Excel.CurrentWorkbook(){[Name = "Таблица2"]}[Content],
   types = Table.TransformColumnTypes(
      source,
      {{"Дата", type date}, {"Сумма на дату", Int64.Type}}
   ),
   table_add_index = Table.AddIndexColumn(types, "Индекс", 0, 1),
   table_add_col = Table.AddColumn(
      table_add_index,
      "Процентное изменение",
      each try
         (
            table_add_index[Сумма на дату]{[Индекс]}
               - table_add_index[Сумма на дату]{[Индекс] - 1}
         )
            / table_add_index[Сумма на дату]{[Индекс] - 1}
      otherwise
         0,
      Percentage.Type
   )
in
   table_add_col

Ранг:

let
   source = Excel.CurrentWorkbook(){[Name = "Таблица3"]}[Content],
   types = Table.TransformColumnTypes(
      source,
      {{"Товар", type text}, {"Продажи", Int64.Type}}
   ),
   fn_rank = (Сумма) =>
      List.Count(List.Select(types[Продажи], each _ > Сумма)) + 1,
   types_ref = types,
   table_add_col = Table.AddColumn(
      types_ref,
      "Ранг",
      each fn_rank([Продажи])
   ),
   table_sort = Table.Sort(table_add_col, {{"Ранг", Order.Ascending}})
in
   table_sort

Количество уникальных клиентов:

let
   source = Excel.CurrentWorkbook(){[Name = "Таблица4"]}[Content],
   types = Table.TransformColumnTypes(
      source,
      {
         {"День", type date},
         {"Клиент", type text},
         {"Сумма клиента", Int64.Type}
      }
   ),
   cols_select = Table.RemoveColumns(types, {"Сумма клиента"}),
   table_group = Table.Group(
      cols_select,
      {"День"},
      {
         {
            "Количество уникальных клиентов",
            each Table.RowCount(Table.Distinct(_)),
            type number
         }
      }
   )
in
   table_group