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

Power Query. Формулы М. Объекты Power Query

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

Описание

В этом уроке мы познакомимся с объектами Power Query, узнаем, что каждый из себя представляет и как создается.

Table — это двумерный массив из строк и столбцов.

Record — это строка таблицы.

List — это таблица из одного столбца или столбец таблицы.

В этом уроке мы узнаем/повторим:

Как создать объект Table из таблицы Excel
Как создать объект List
Как преобразовать List в Table
Как преобразовать Table в List
Как создать список списков или List of Lists
Как создать объект Record
Как создать список из записей или List of Records
Как создать Table из Record
Как сослаться на Record из Table
Как преобразовать таблицу в список записей или List of Records

Объект таблица

Подключимся к таблице на листе Excel.

Excel.CurrentWorkbook(){[Name="ПримерТаблицы"]}[Content]

Объект список

Создадим список из разных чисел.

{1,2,3,4,5,6,7,8,9,10}

Создадим список из списков.

{{1, "Кот"}, {2, "Пёс"}, {3, "Джигурда"}}

Создать список из чисел, идущих подряд.

{1..365}

Преобразуем список в таблицу

let
   create_list = {"iosif, stalin", "lavrentiy, beria"}, 
   tab_from_list = Table.FromList(
      create_list, 
      Splitter.SplitTextByDelimiter(","), 
      null, 
      null, 
      ExtraValues.Error
   ), 
   cols_types = Table.TransformColumnTypes(
      tab_from_list, 
      {{"Column1", type text}, {"Column2", type text}}
   )
in
   cols_types

Список уникальных значений столбца таблицы

let
   get_table = Excel.CurrentWorkbook(){[Name = "ПримерТаблицы"]}[Content], 
   Товар = get_table[Товар], 
   list_distinct = List.Distinct(#"Товар")
in
   list_distinct

Объект Record

Создадим запись из двух полей.

[ТабельныйНомер=666, Рабочий="Виктор"]

Создать таблицу из списка записей

let
   create_records = {
      [EmployeeID = 1, EmployeeName = "Fred"], 
      [EmployeeID = 2, EmployeeName = "John"], 
      [EmployeeID = 3, EmployeeName = "Jane"], 
      [EmployeeID = 4, EmployeeName = "Mary"]
   }, 
   tab_fromlist = Table.FromList(
      create_records, 
      Splitter.SplitByNothing(), 
      null, 
      null, 
      ExtraValues.Error
   ), 
   col_expand = Table.ExpandRecordColumn(
      tab_fromlist, 
      "Column1", 
      {"EmployeeID", "EmployeeName"}, 
      {"EmployeeID", "EmployeeName"}
   )
in
   col_expand

Ссылка на запись таблицы

Сошлемся на запись (строку) таблицы.

let
    get_table = Excel.CurrentWorkbook(){[Name="ПримерТаблицы"]}[Content],
    get_record = get_table{0}
in
    get_record

Сослаться на значение определенных столбца и строки таблицы

Получим первое значение из столбца Товар.

let
    get_table = Excel.CurrentWorkbook(){[Name="ПримерТаблицы"]}[Content],
    get_value = get_table{0}[Товар]
in
    get_value

Создать столбец записей для таблицы

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

let
   get_table = Excel.CurrentWorkbook(){[Name = "ПримерТаблицы"]}[Content], 
   tab_add_index = Table.AddIndexColumn(get_table, "Индекс", 0, 1), 
   tab_add_records_col = Table.AddColumn(
      tab_add_index, 
      "Records", 
      each tab_add_index{[Индекс]}
   ), 
   col_select = Table.SelectColumns(tab_add_records_col, {"Records"})
in
   col_select

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

Excel.CurrentWorkbook
Table.FromList
Splitter.SplitTextByDelimiter
ExtraValues.Error
Table.TransformColumnTypes
List.Distinct
Splitter.SplitByNothing
Table.ExpandRecordColumn
Table.AddIndexColumn
Table.SelectColumns

Этот урок входит в Продвинутый курс 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. Формулы М. Объект List 1

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