Главная Power Query Power Query Продвинутый №4. Объекты Power Query
Power QueryPower Query. Продвинутый курс

Power Query Продвинутый №4. Объекты 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

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

Вас может заинтересовать

Power Query Практический №1. Обработка типичной выписки

Power Query. Интервальный просмотр 1 (ВПР 1)

Power Query Ускорение запроса №3. Уменьшить количество подключений

Power Query Веб-запросы №3. Многостраничное извлечение

Power Query. Информация о формате, Чтение zip

Power Query. Добавить столбец в каждую таблицу табличного столбца

Power Query Базовый №20. Фильтрация параметром

Power Query Ошибки №19. Неверная разбивка на столбцы

Power Query Ошибки №11. Подключиться к последнему файлу

Power Query Базовый №1. Зачем нужен Power Query, Основные возможности