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