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

Power Query. Формулы М. List.Generate, Нарастающий итог 5

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

Описание

В этом уроке мы разберем функцию List.Generate и сделаем при помощи этой функции нарастающий итог, который будет самым быстрым нарастающим итогом из изученных нами.

Функция List.Generate представляет из себя цикл While. Цикл While выполняется до тех пор, пока условие истинно.

Разберем пример с нарастающим итогом.

Исходные данные: столбец с 50000 числовых значений.

Решение

Нам понадобится подключиться к этом столбцу и преобразовать его в список. Этот список нужно забуферовать при помощи List.Buffer.

Создать еще 1 пустой запрос в котором при помощи List.Count посчитать количество значений в списке с числами.

Далее вводим функцию List.Generate:

Параметр ititial. В нашем случае введем запись, где index — это номер итерации, а total — это текущее значение нарастающего итога. Самый первый индекс — это 0, а первое значение нарастающего итога — это первое значение из списка с числами;
Параметр condition. Показывает до каких пор выполняется цикл. Цикл выполняется до тех пор пока index < количества значений в списке с числами;
Параметр next. Показывает, что должно произойти, если condition = true. В нашем случае нарастающий итог должен увеличиться на значение с индексом index из списка с числами.

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

List.Buffer
List.Count
List.Generate
Table.FromList
Splitter.SplitByNothing
ExtraValues.Error
Table.ExpandRecordColumn

Код

Подключаемся к источнику:

let
   source = List.Buffer(
      Excel.CurrentWorkbook(){[Name = "Числа"]}[Content][Числа]
   )
in
   source

Считаем количество элементов в исходном списке:

let
   source = List.Count(Список)
in
   source

Нарастающий итог:

let
   source = List.Generate(
      // Начальное положение: Индекс = 0, Сумма = первый элемент списка
      () => [index = 0, total = Список{index}],
      // Условие при котором выполняется действие: Если Индекс < Количества элементов в списке
      each [index] < Счетчик,
      // Если условие верно: Индекс увеличивается на единицу, а к Сумме прибавляется элемент списка с Индексом index
      each [index = [index] + 1, total = [total] + Список{index}]
   ),
   to_table = Table.FromList(
      source,
      Splitter.SplitByNothing(),
      null,
      null,
      ExtraValues.Error
   ),
   col_expand_records = Table.ExpandRecordColumn(
      to_table,
      "Column1",
      {"index", "total"},
      {"index", "total"}
   )
in
   col_expand_records

Этот урок входит в Продвинутый курс 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. PQ + VBA. Массовая обработка файлов

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