Файлы к уроку:
Все уроки Продвинутого курса Power Query на этой странице и в этом плейлисте.
Описание
В этом уроке мы выполним 4 задачи на вычисления в Power Query и Power Pivot:
- Процент от общей суммы по столбцу
- Процентное изменение
- Ранг
- Количество уникальных значений
Решение
Процент от общей суммы
Чтобы найти процент от общей суммы сначала создадим столбец, в котором в каждой строке будет находиться общая сумма.
Далее останется только разделить значение в каждой строке на значение в созданном столбце.
Изменение
Чтобы вычислить разницу между значением в текущей строке на значение в предыдущей надо сначала создать столбец индекса.
Именно значение из столбца индекса позволяет нам ссылаться на любую строку таблицы.
Ранг
Для вычисления ранга мы создадим пользовательскую функцию, которая посчитает сколько значений в столбце выше значения в строке.
Уникальные значения
Для вычисления уникального значения мы воспользуемся операцией группировки с функцией 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