Power Query. Формулы М. Разделить столбец на строки, Расширенные возможности Text.Trim

Описание

В источнике данных перечень купленных товаров для каждого заказа находится в одной строке, например:

Samsung Galaxy A6 32Gb (черный) SM-A600FZKNSER — 15990 Р, 1 шт.; Защитное стекло 5D для Samsung Galaxy S9+ черная рамка 38063 — 900 Р, 1 шт.

Нужно разделить столбец таким образом, чтобы каждый проданный товар был в отдельной строке. Кроме того, нужно разделить полученный столбец на 3 части: название товара, Цена, Количество.

Решение

Чтобы решить задачу мы сначала разделим этот столбец по разделителю, а потом развернем полученный список в новые строки.

Для решения мы воспользуемся следующим функционалом:

• Разделить столбец по разделителю
• Text.Trim

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

• Table.PromoteHeaders
• Table.ExpandListColumn
• Table.TransformColumns
• Splitter.SplitTextByDelimiter
• QuoteStyle.Csv
• Table.SelectRows
• Table.SplitColumn
• Splitter.SplitTextByEachDelimiter
• Text.Trim
• Text.Start
• Table.TransformColumnTypes
• Int64.Type

Код

let
   source = Csv.Document(
      File.Contents(Путь & "И_CRM.csv"), 
      [
         Delimiter  = ";", 
         Columns    = 12, 
         Encoding   = 65001, 
         QuoteStyle = QuoteStyle.None
      ]
   ), 
   tab_headers = Table.PromoteHeaders(source, [PromoteAllScalars = true]), 
   col_expand = Table.ExpandListColumn(
      Table.TransformColumns(
         tab_headers, 
         {
            {
               "Состав", 
               Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), 
               let
                  itemType = (type nullable text)
                     meta [Serialized.Text = true]
               in
                  type {itemType}
            }
         }
      ), 
      "Состав"
   ), 
   rows_select = Table.SelectRows(
      col_expand, 
      each [Состав] <> null and [Состав] <> ""
   ), 
   col_split = Table.SplitColumn(
      rows_select, 
      "Состав", 
      Splitter.SplitTextByEachDelimiter({" - "}, QuoteStyle.Csv, true), 
      {"Товар", "Состав.2"}
   ), 
   col_split_2 = Table.SplitColumn(
      col_split, 
      "Состав.2", 
      Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
      {"Цена", "Количество"}
   ), 
   col_transform = Table.TransformColumns(
      col_split_2, 
      {
         {"Товар", Text.Trim, type text}, 
         {"Цена", each Text.Trim(_, {"а" .. "я", "А" .. "Я"}), type text}, 
         {
            "Количество", 
            each Text.Trim(_, {"а" .. "я", "А" .. "Я", "."}), 
            type text
         }
      }
   ), 
   col_transform_2 = Table.TransformColumns(
      col_transform, 
      {{"Дата и время", each Text.Start(_, 10), type text}}
   ), 
   col_type = Table.TransformColumnTypes(
      col_transform_2, 
      {{"Цена", type number}}, 
      "en-US"
   ), 
   cols_type = Table.TransformColumnTypes(
      col_type, 
      {
         {"Количество", Int64.Type}, 
         {"Цена", type number}, 
         {"Дата и время", type date}
      }
   )
in
   cols_type