Power Query Практический №16. ABC анализ

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

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

• YouTube
• VK

Ссылки:

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

Описание

В этом уроке мы выполним ABC анализ в Power Query.

Решение

Задача решается в несколько шагов.

• Подключение
• Отфильтровать нужный период
• Группировка по полю с названием товара
• Для каждой суммы найти сумму продаж >= этой суммы
• Найти кумулятивной суммы найти долю от общей суммы
• Назначить имена классов и отсортировать таблицу по возрастанию

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

• Csv.Document
• File.Contents
• QuoteStyle.None
• Table.PromoteHeaders
• Table.TransformColumnTypes
• Int64.Type
• Table.SelectRows
• Date.Year
• List.Max
• Table.Group
• List.Sum
• List.Buffer
• Table.AddColumn
• List.Select
• Table.Sort
• Order.Ascending

Код

let
   source = Csv.Document(
      File.Contents(
         filepath
      ), 
      [Delimiter = ",", Columns = 25, Encoding = 1251, QuoteStyle = QuoteStyle.None]
   ), 
   tab_headers = Table.PromoteHeaders(source, [PromoteAllScalars = true]), 
   cols_types = Table.TransformColumnTypes(
      tab_headers, 
      {
         {"YEAR_ID", Int64.Type}, 
         {"MONTH_ID", Int64.Type}, 
         {"QTR_ID", Int64.Type}, 
         {"ORDERDATE", type datetime}, 
         {"SALES", type number}, 
         {"ORDERLINENUMBER", Int64.Type}, 
         {"PRICEEACH", type number}, 
         {"QUANTITYORDERED", Int64.Type}, 
         {"ORDERNUMBER", Int64.Type}
      }, 
      "en_US"
   ), 
   rows_select = Table.SelectRows(
      cols_types, 
      let
         latest = Date.Year(List.Max(cols_types[ORDERDATE]))
      in
         each Date.Year([ORDERDATE]) = latest
   ), 
   tab_groupby = Table.Group(
      rows_select, 
      {"PRODUCTCODE"}, 
      {{"Sales", each List.Sum([SALES]), type nullable number}}
   ), 
   list_sales = List.Buffer(tab_groupby[Sales]), 
   tab_groupby_ref = tab_groupby, 
   tab_add_col_1 = Table.AddColumn(
      tab_groupby_ref, 
      "Cumulative", 
      each List.Sum(List.Select(list_sales, (list_item) => list_item >= [Sales]))
   ), 
   tab_add_col_2 = Table.AddColumn(
      tab_add_col_1, 
      "Percentage", 
      each [Cumulative] / List.Sum(list_sales)
   ), 
   tab_add_col_3 = Table.AddColumn(
      tab_add_col_2, 
      "Class", 
      each 
         if [Percentage] < 0.7 then
            "A"
         else if [Percentage] < 0.9 then
            "B"
         else
            "C"
   ), 
   tab_sort = Table.Sort(tab_add_col_3, {{"Percentage", Order.Ascending}})
in
   tab_sort

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