Power Query. Практика. Количество позиций в строке

Все уроки Практического курса на этой странице и в этом плейлисте.

Описание

Наша задача — это посчитать количество товаров, которые перечислены в ячейке. Товары перечислены через точку с запятой.

Мы научимся решать эту задачу двумя способами.

Решение

Способ 1. Для первого способа мы используем функции Text.Split и List.Count. Сначала при помощи Text.Split мы создадим из ячейки список. Text.Split создает список из строки. Потом при помощи List.Count мы посчитаем количество значений в получившемся списке.

Способ 2. Второй способ — это привычный способ для пользователей Excel. В Excel аналогичную задачу мы решаем при помощи функций ДЛСТР и ПОДСТАВИТЬ. Сначала функцией ПОДСТАВИТЬ мы убираем все точки с запятой. Потом функцией ДЛСТР считаем количество символов в исходном столбце и столбце без точек с запятой.

В Power Query есть аналоги привычных нам функций. Text.Remove в данном случае поможет нам избавиться от точек с запятой, а Text.Length посчитает количество символов в текстовой строке.

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

• Csv.Document
• File.Contents
• QuoteStyle.None
• Table.PromoteHeaders
• Table.SelectColumns
• Table.SelectRows
• Text.Split
• List.Count
• Text.Remove
• Text.Length

Код

Первый способ:

let
   source = Csv.Document(
      File.Contents(path & "И_CRM.csv"),
      [
         Delimiter  = ";",
         Columns    = 12,
         Encoding   = 65001,
         QuoteStyle = QuoteStyle.None
      ]
   ),
   table_promote_headers = Table.PromoteHeaders(
      source,
      [PromoteAllScalars = true]
   ),
   cols_select = Table.SelectColumns(
      table_promote_headers,
      {"Сумма", "Номер", "Дата и время", "Статус заказа", "Состав"}
   ),
   rows_select = Table.SelectRows(
      cols_select,
      each ([Статус заказа] = "Выполнен")
   ),
   table_add_column_1 = Table.AddColumn(
      rows_select,
      "Text.Split",
      each Text.Split([Состав], ";")
   ),
   table_add_column_2 = Table.AddColumn(
      table_add_column_1,
      "Количество позиций",
      each List.Count([Text.Split])
   )
in
   table_add_column_2

Второй способ:

let
   source = Csv.Document(
      File.Contents(path & "И_CRM.csv"),
      [
         Delimiter  = ";",
         Columns    = 12,
         Encoding   = 65001,
         QuoteStyle = QuoteStyle.None
      ]
   ),
   table_promote_headers = Table.PromoteHeaders(
      source,
      [PromoteAllScalars = true]
   ),
   cols_select = Table.SelectColumns(
      table_promote_headers,
      {"Сумма", "Номер", "Дата и время", "Статус заказа", "Состав"}
   ),
   rows_select = Table.SelectRows(
      cols_select,
      each ([Статус заказа] = "Выполнен")
   ),
   table_add_col_1 = Table.AddColumn(
      rows_select,
      "Без ;",
      each Text.Remove([Состав], ";")
   ),
   table_add_col_2 = Table.AddColumn(
      table_add_col_1,
      "Длина оригинального",
      each Text.Length([Состав])
   ),
   table_add_col_3 = Table.AddColumn(
      table_add_col_2,
      "Длина без ;",
      each Text.Length([#"Без ;"])
   ),
   table_add_col_4 = Table.AddColumn(
      table_add_col_3,
      "Количество позиций",
      each [Длина оригинального] - [#"Длина без ;"] + 1
   )
in
   table_add_col_4

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