Power Query. Формулы М. Ссылки 4

---

Описание

• В этом уроке мы повторим/научимся ссылаться на ячейку, которая находится в другом шаге запроса
• При выполнении задачи мы повторим следующие формулы Power Query:
  • Text.BetweenDelimiters
  • Date.From
  • Table.AddColumn

Решение

При открытии файла у вас уже есть 2 шага: Путь и Источник. Добавляем еще несколько шагов:

1. Удаление верхних строк. Можно при помощи UI (Главная — Сократить строки — Удалить строки — Удаление верхних строк), а можно формулой =Table.Skip(Таблица,6) (здесь и далее «Таблица — это название шага, где хранится нужная таблица»)
2. Используем первую строку в качестве заголовка. В UI (Главная — Преобразование — Использовать первую строку в качестве заголовков ) или при помощи формулы =Table.PromoteHeaders(Таблица)
3. Удалить строку итогов в самом низу. Отфильтровать можно при помощи пользовательского интерфейса или формулой =Table.SelectRows(Таблица, each [Pos ID] <> «Итого»)
4. Указать типы данных для столбцов. Здесь главное у последних двух столбцов указать тип данных числовой

Теперь мы подошли к самому главному шагу, ради которого все затевалось. Здесь и начинается практика ссылок.

Дата находится в шаге Источник в столбце Column1 в строке 4. Значит именно на этот шаг, столбец и строку нужно сослаться.

Добавляем столбец: Вкладка Добавление столбца — Настраиваемый столбец. Вводим ссылку Источник[Column2]{3}. Жмем ОК. Почти получилось, но помимо даты много лишнего. Как исправить? Воспользоваться формулой Text.BetweenDelimiters.

Исправляем формулу на следующую Text.BetweenDelimiters(Источник[Column2]{3}, «: «, » «). Первый аргумент — это сама ссылка, второй — это первый разделитель, третий — второй разделитель. Получилось? Не совсем. Теперь нужно преобразовать получившееся значение в дату.

Еще раз исправляем формулу на следующую Date.From(Text.BetweenDelimiters(Источник[Column2]{3}, «: «, » «)). Почти готово, но почему-то Power Query тип данных в этом столбце понимает как общий.

Тогда в строке формул добавим аргумент, указывающий тип данных. Итоговая формула в столбце формул будет выглядеть так: =Table.AddColumn(#»Измененный тип», «Дата», each Date.From(Text.BetweenDelimiters(Источник[Column2]{3}, «: «, » «) ), type date)

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

• Table.Skip
• Table.PromoteHeaders
• Table.SelectRows
• Table.TransformColumnTypes
• Table.AddColumn
• Date.From
• Text.BetweenDelimiters

Код

let
   path = Excel.CurrentWorkbook(){[Name = "Путь"]}[Content][Column1]{0}, 
   source = Csv.Document(
      File.Contents(path & "И_Остатки.txt"), 
      [
         Delimiter  = "	", 
         Columns    = 10, 
         Encoding   = 65001, 
         QuoteStyle = QuoteStyle.None
      ]
   ), 
   rows_skip = Table.Skip(source, 6), 
   tab_promote_headers = Table.PromoteHeaders(
      rows_skip, 
      [PromoteAllScalars = true]
   ), 
   rows_select = Table.SelectRows(
      tab_promote_headers, 
      each ([Pos ID] = "")
   ), 
   cols_types = Table.TransformColumnTypes(
      rows_select, 
      {{"Остаток на складе", type number}, {"Себестоимость", type number}}
   ), 
   tab_add_col = Table.AddColumn(
      source, 
      "Дата", 
      each Date.From(Text.BetweenDelimiters(source[Column2]{3}, ": ", " ")), 
      type date
   )
in
   tab_add_col