Power Query Практический №8. Скученные данные 3

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

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

• YouTube
• VK

Ссылки:

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

Описание

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

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

Решение

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

Значения копии столбца мы преобразуем в дату. В исходном столбце мы заменим значения таким образом, чтобы они превратились в null в случае, когда в копии находится дата.

Далее останется лишь удалить лишние строки и переупорядочить столбцы.

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

• Excel.Workbook
• File.Contents
• Table.SelectColumns
• Table.ExpandTableColumn
• Table.PromoteHeaders
• Table.Skip
• Table.DuplicateColumn
• Table.TransformColumnTypes
• Table.ReplaceErrorValues
• Table.ReplaceValue
• Replacer.ReplaceValue
• Table.FillDown

Код

let
   source = Excel.Workbook(File.Contents(path), null, true),
   cols_select = Table.SelectColumns(source, {"Data"}),
   col_expand_table = Table.ExpandTableColumn(
      cols_select,
      "Data",
      {"Column1", "Column2"}
   ),
   table_promote_headers = Table.PromoteHeaders(
      col_expand_table,
      [PromoteAllScalars = true]
   ),
   table_skip_rows = Table.Skip(table_promote_headers, 8),
   table_promote_headers_2 = Table.PromoteHeaders(
      table_skip_rows,
      [PromoteAllScalars = true]
   ),
   col_duplicate = Table.DuplicateColumn(
      table_promote_headers_2,
      "Номенклатура",
      "Дата"
   ),
   types = Table.TransformColumnTypes(col_duplicate, {{"Дата", type date}}),
   col_replace_errors = Table.ReplaceErrorValues(types, {{"Дата", null}}),
   col_replace_values = Table.ReplaceValue(
      col_replace_errors,
      each [Номенклатура],
      each if [Дата] = null then [Номенклатура] else null,
      Replacer.ReplaceValue,
      {"Номенклатура"}
   ),
   table_fill_down = Table.FillDown(col_replace_values, {"Номенклатура"}),
   types_2 = Table.TransformColumnTypes(
      table_fill_down,
      {{"Кон. остаток", type number}},
      "en-US"
   ),
   col_replace_values_2 = Table.ReplaceValue(
      types_2,
      null,
      0,
      Replacer.ReplaceValue,
      {"Кон. остаток"}
   ),
   rows_select = Table.SelectRows(
      col_replace_values_2,
      each ([Дата] <> null)
   ),
   table_reorder_cols = Table.ReorderColumns(
      rows_select,
      {"Дата", "Номенклатура", "Кон. остаток"}
   )
in
   table_reorder_cols

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