Power Query Power Query. Продвинутый курс Формулы М

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

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

Описание

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

Решение

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

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

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

Дата находится в шаге Источник в столбце 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

Этот урок входит в Продвинутый курс Power Query

Номер урока	Урок	Описание
1	Введение ч. 1	В этом уроке вы вкратце узнаете как устроен язык M / Power Query
2	Введение ч. 2	В этом уроке вы узнаете про ключевые слова let, in и узнаете как устроены функции M / Power Query.
3	Введение ч. 3	В этом уроке я расскажу какие преимущества вам даст владение языком формул M / Power Query
4	Объекты Power Query	В этом уроке мы познакомимся с объектами Power Query: Table, List, Record.
5	Объект List ч. 1	В этом уроке мы начнем знакомиться с объектом List в Excel Power Query.
6	Объект List ч. 2	Вы узнаете как создавать листы, как ссылаться на элементы листа и изучим несколько полезных формул листа.
7	Объект Record (Запись)	Record — это набор пар ключ-значение. Каждый Record — это строка таблицы.
8	Объект Table	В этом уроке мы познакомимся с объектом Table в Power Query. Что это такое и какие основные функции применяются к этому объекту.
9	Ссылки ч. 1	В этом уроке из курса по Power Query мы повторим как ссылаться на строки таблицы.
10	Ссылки ч. 2	В этом уроке из курса обучения Power Query будем практиковать ссылки.
11	Ссылки ч. 3	В этом уроке курса по Power Query мы повторим ссылки на запись, ссылки на значение, ссылки на другую строку.
12	Ссылки ч. 4	В этом уроке мы повторим/научимся ссылаться на ячейку, которая находится в другом шаге запроса.
13	Пользовательские функции	В этом уроке мы научимся создавать пользовательские функции.
14	Работа с датой и временем, #datetime, #duration	В этом уроке по Excel Power Query вы научитесь пользоваться функциями #duration и #datetime для создания даты/времени и длительности.
15	Аналоги текстовых функций Excel	В этом уроке курса мы изучим Power Query аналоги текстовых функций Excel.
16	Группировка, Пивот, Анпивот, Сортировка	В этом уроке по формулам Power Query мы разберем функции группировки, сведения, отмены сведения, сортировки и фильтра.
17	Нюансы консолидации из разных книг/листов Excel и List PositionOf	В этом уроке мы узнаем о некоторых нюансах объединения таблиц по вертикали из разных файлов.
18	Разделить столбец на строки, Text.Trim	В этом уроке мы разделим текстовый столбец на строки. Данные скучены в одной ячейке. Нужно разбить ее на несколько.
19	Заголовки в двух строках, List.Zip	В этом уроке мы обработаем заголовки, которые находятся в двух строках. Нам понадобится функция List.Zip
20	Вычисления в Power Query	В этом уроке мы научимся выполнять вычисления в Power Query и в Power Pivot.
21	Скользящее среднее, List Range	В этом уроке мы научимся считать скользящее среднее в Power Query.
22	Агрегирование текста, группировка	В этом уроке вы научитесь выполнять группировку с агрегированием текстовых значений в Power Query.
23	Table.TransformColumns	В этом уроке мы изучим функцию Table.TransformColumns.
24	Разгруппировка/Скрытые возможности Table.ReplaceValue	В этом уроке мы научимся делать разгруппировку данных в Power Query, а также изучим скрытые возможности функции Table.ReplaceValue.
25	Функция List.Accumulate	В этом уроке мы с нуля разберем функцию List.Accumulate. Сначала я объясню цикл For, а потом мы закрепим знания на несложных, но очень интересных примерах.
26	Множественная замена текста c List Accumulate и VBA	В этом уроке мы научимся делать множественную текстовую замену с помощью Power Query.
27	Функции Text.Format, List.Generate, List.Accumulate	В этом уроке мы сгенерируем список веб-страниц, в которых будет меняться параметр id.
28	Множественная текстовая замена c List.Generate	В этом уроке мы научимся выполнять множественную текстовую замену в Power Query с помощью функции List.Generate.
29	List.Generate 2, график платежей по кредиту	Создадим при помощи этой функции целую таблицу, которая будет представлять из себя график платежей по кредиту.
30	List.Generate, Нарастающий итог 5	Выполним нарастающий итог, который в сотни раз быстрее предыдущих версий нарастающего итога.

предыдущая запись

Power Query. Введение. Знакомство с Power Query

следующая запись