Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):

• Синтаксис функции List.Accumulate
• Разбор базового практического примера
• Второй параметр List.Accumulate — начальное значение сводного значения
• Третий параметр List.Accumulate — пользовательская функция
• Практический пример: создание в Power Query на языке M списка с наименованием месяцев

---

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В этой статье мы поговорим про функцию List.Accumulate, входящую в состав редактора запросов Power Query (языка M) для Excel (Эксель) и Power BI.

Данная функция итерационно накапливает сводное значение из элементов списка с предварительной обработкой этих элементов пользовательской функцией при их последовательном переборе. Итак, давайте разбираться с ней по порядку. Для начала разберем синтаксис, далее примеры, ну и напоследок, разберем на практике полезный пример на основе List.Accumulate, который позволит Вам в Power Query на языке M с легкостью, буквальной одной небольшой строчкой кода, создавать список из наименований месяцев.

Да, и еще один момент, в рамках распродажи до 30 апреля 2024 г. у Вас имеется возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 50% (вместо 10000, всего за 5000 руб.)

В этом видеокурсе язык DAX преподнесен как простой конструктор, состоящий из нескольких блоков, которые имеют свое определенное, конкретное предназначение. Сочетая различными способами эти блоки, Вы, при помощи конструктора формул DAX, с легкостью сможете решать любые (простые или сложные) аналитические задачи.

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 50% (до 30 апреля 2024 г.): узнать подробнее

до конца распродажи осталось:

 

Синтаксис функции List.Accumulate

Синтаксис:

List.Accumulate({Список}, НачальноеЗначение, Функция(x, y))

Где:

• {Список} — любой список, элементы которого путем последовательного перебора нужно обработать пользовательской функцией и в результате накопить общее сводное значение;
• НачальноеЗначение — необязательный параметр. Начальное (стартовое) значение операции сведения элементов Списка;
• Функция(x, y) — пользовательская функция, которая задает порядок сведения элементов Списка. Во входящих значениях функции должны быть два параметра (x, y), в которые List.Accumulate передает текущий накопленный результат сводного значения (x) и перебираемое значение из Списка (y).

 

Разбор базового практического примера

Давайте на практике рассмотрим функцию List.Accumulate путем разбора нескольких примеров. Но начнем мы с базового примера, который приводится в справке Power Query:

= List.Accumulate({1, 2, 3, 4, 5}, 0, (state, current) => state + current)

Запишем эту формулу в более привычный вид:

= List.Accumulate({1, 2, 3, 4, 5}, 0, (x, y) => x + y)

Где:

• {1, 2, 3, 4, 5} — исходный Список, с элементами которого мы будем работать;
• 0 — начальное значение операции сведения элементов Списка;
• (x, y) => x + y — пользовательская функция сведения;
• x — текущий накопленный результат сводного значения;
• y — перебираемое значение из Списка.

Результатом выполнения данной формулы будет значение 15:

 

Давайте разбираться почему…

Итак, функция List.Accumulate в Power Query (в языке M) работает итерационно, перебирая последовательно каждый элемент списка и для каждого из этих элементов выполняет пользовательскую функцию. Причем на каждом шаге итерации, List.Accumulate передает в эту функцию 2 параметра: «x» — текущий накопленный результат сводного значения и «y» — перебираемое значение из Списка.

Чтобы понять суть всего происходящего, давайте процесс выполнения этого примера рассмотрим прямо по шагам. Всего шагов будет 5, так как в исходном Списке 5 элементов:

1. List.Accumulate берет из Списка первый элемент, и это цифра 1.

   Далее, из второго параметра берет начальное значение операции сведения. Данный параметр необязательный и может быть пустым (ниже мы рассмотрим такие примеры), но в нашем случае, это цифра 0.

   Далее, для этих взятых значений 1 и 0, начинает выполнять пользовательскую функцию (x, y) => x + y.

   В нашем случае «x»  — это текущий накопленный результат сводного значения. А так как функция выполняет только первую итерацию, то никакого накопленного результата нет, а значит просто берется то самое начальное значение операции сведения, то есть x = 0, второму параметру функции List.Accumulate.

   Ну и «y» — это перебираемое значение из Списка. На пером шаге итерации y = 1, так как функция работает с первым элементом Списка.

   Итого, пользовательская функция (x, y) => x + y принимает вид (0, 1) => 0 + 1 и выдает результат = 1. Это значение запоминается, как текущий накопленный результат сводного значения, то есть, теперь x будет равен 1. И функция List.Accumulate переходит ко второму шагу итерации, а то есть, начинает работу со вторым элементом Списка;

2. На втором шаге x=1 (напомню, это текущий накопленный результат их первого шага) и y = 2 (так как второй элемент Списка = 2). Пользовательская функция (x, y) => x + y принимает вид: (1, 2) => 1 + 2. Результат вычисления этой функции = 3. Данное значение опять запоминается в переменную «x», как текущее накопленное значение и функция List.Accumulate опять переходит к следующему шагу итерации (к следующему элементу Списка);
3. x = 3; y = 3; Пользовательская функция (x, y) => x + y принимает вид (3, 3) => 3 + 3. Итог вычисления = 6. Переменная «x» меняет свое значение и становится равной 6;
4. x = 6; y = 4; Пользовательская функция (x, y) => x + y принимает вид (6, 4) => 6 + 4. Итог вычисления = 10. Переменная «x» меняет свое значение и становится равной 10;
5. x = 10; y = 5; Пользовательская функция (x, y) => x + y принимает вид (10, 5) => 10 + 5. Итог вычисления = 15

Ну и, как итог, функция языка M — List.Accumulate в Power Query нам выдает результат 15.

 

Второй параметр List.Accumulate — начальное значение сводного значения

Хорошо, с основной схемой работы функции List.Accumulate мы разобрались, теперь давайте более детально обсудим второй параметр List.Accumulate:

 

Как мы уже видели, именно со значения этого параметра и начинается вычисляться накопленное значение. То есть, на втором и последующих итерациях List.Accumulate, накопленное значение равно результату вычисления предыдущего шага итерации. Но на первом шаге итерации естественно нет никаких предыдущих результатов вычисления. Так вот при помощи этого параметра мы имеем возможность задать его. В примере выше это значение мы задали как 0. Но, можно было бы задать и не 0, а, например, 1. Тогда, все вычисление у нас изменится согласно этому значению:

 

Теперь результатом вычисления формулы стало значение 16, на 1 больше, чем в прошлый раз. Почему? Потому что на самом первом шаге итерации в этот раз «x» уже равнялся не 0, как в прошлый раз, а 1. И поэтому вся итоговая сумма сводного значения увеличилась на 1.

Тоже самое будет, если мы наоборот уменьшим на 1 этот параметр, то есть, если вместо 0, мы в этом парметре поставим -1, то результат уменьшится на единицу, по сравнению с самым первым вариантом нашей формулы:

 

Также, в описании синтаксиса List.Accumulate я писал, что второй параметр необязательный. То есть, мы не обязаны этой функции указывать с какого значения нужно начинать сведение. В замен этого, мы можем просто указать пустоту — null (отсутствие значения). В данном примере с числами — null не будет иметь никакого смысла, так как все математические операции с null дают результат null:

 

Но, если у нас будут не числа, а, например, текст или что-то другое (таблицы), то вместо null мы сможем подставить пустой текст «», или пустую таблицу #table({}, {}) и тогда это будет иметь смысл. Ниже мы посмотрим на эти примеры.

 

Третий параметр List.Accumulate — пользовательская функция

Теперь давайте немного поговорим о третьем параметре функции List.Accumulate — о пользовательской функции. Данная функция может быть любая. Мы вместо сложения x + y можем использовать умножение, вычитание, деление:

 

И вообще, операции могут быть гораздо сложнее. Но, главное, чтобы во входящих параметрах Вашей пользовательской функции было 2 значения (x, y). Даже не обязательно их в самой функции использовать, главное, чтобы они были именно во входящих параметрах.

 

В примере в пользовательской функции в параметры было передано только одно значение, вместо двух, что и вызвало в Power Query (в языке M) ошибку. Так как пользовательская функция ожидает только 1 значение, а List.Accumulate — в автоматическом режиме передает два значения.

Но, если мы в параметрах пользовательской функции укажем 2 входящих параметра, а в самой функции их использовать не будем, то все отработает без ошибок:

 

В этом примере ошибок нет, так как во входящих параметрах пользовательской функции 2 параметра. И опять же, ошибок нет даже несмотря на то, что в теле самой функции мы использовали всего-лишь один из переданных параметров, а именно переменную «x». Именно из-за этого у нас итоговое значение получилось равным 1, так как на первой итерации в «x» была подставлена 1 из второго параметра List.Accumulate и далее это значение «x» больше уже не менялось, так как менять его было не чем, потому что переменной «y» у нас нет. А именно она и должна была подставлять значения из Списка, чтобы «x» от этого меняла свое значение из предыдущего шага итерации.

Как выше я уже вскользь упоминал, List.Accumulate умеет накапливать не только числовые элементы Списка. Давайте попробуем сделать накопление текстовых данных:

= List.Accumulate({"Маша", "Миша", "Петя"}, "", (x, y) => x & y & "; ")

Результатом выполнения такой формулы будет одно текстовое значение — объединенная текстовая строка:

 

Практический пример: создание в Power Query на языке M списка с наименованием месяцев

Хорошо, в общем и целом с функцией List.Accumulate мы разобрались, теперь давайте применим ее уже в полезном практическом примере. Итак, задача — нам нужно сформировать список из наименования месяцев. И этот список нужно сделать легко и быстро при помощи всего одной строки кода. А, как известно, Power Query (язык M) нам не дает возможности в автоматическом режиме построить такой список. Поэтому мы его создадим сами. И в этом нам очень сильно поможет List.Accumulate. Формула будет такой:

= List.Accumulate({1..12}, {}, (x, y)=> x & {Date.MonthName(#date(2019, y, 01))})

Здесь в первый параметр функции List.Accumulate мы передали Список из 12 цифр (запись 1..12 — это возможность в Power Query (в языке M) кратко записать Список из последовательных целых чисел, начиная с 1 и заканчивая 12). Именно по элементам этого Списка List.Accumulate и будет совершать свои итерационные шаги.

Далее, во второй параметр List.Accumulate мы вставили пустой список. Как мы помним, второй параметр отвечает за стартовое значение сводимой информации. И в нашем случае стартовое значение — пустой список. Почему именно список? Потому что в итоге нам нужно получить именно Список (с наименованиями месяцев). Если бы нам нужно было получить текстовую строку, то можно было бы написать пустую стоку «», если бы нам нужна была таблица, то в качестве стартового значения можно было бы прописать пустую таблицу #table({}, {}).

Как Вы понимаете, самое главное во всей этой формуле — наша пользовательская функция, которая работает отдельно с каждым элементом Списка. Итак, на первом шаге берется первый элемент Списка — цифра 1. Затем берется стартовое значение — пустой список {}. Затем эти значения передаются на вход нашей пользовательской функции, где прописано следующее: x & {Date.MonthName(#date(2019, y, 01))}.

То есть, в качестве «x» — у нас подставляется пустой Список {} и он объединяется при помощи оператора соединения списков & с другим Списком {Date.MonthName(#date(2019, y, 01))}. Такое объединение Списков {} & {} — дает нам новый расширенный Список со всеми строками обоих списков. То есть, в нашем случае пустой Список (без строк) объединяется со вторым списком. В этом втором Списке находится 1 текстовая строка — наименование месяца, которое возвращает функция Date.MonthName из даты #date(2019, y, 01). В свою очередь дата у нас формируется с нужным нам месяцем, так как в качестве параметра месяца в дату мы передаем переменную «y», которая, как мы помним, содержит текущий перебираемый элемент из Списка в первом параметре List.Accumulate.

В итоге в переменную «x» на первом шаге записывается Список из одной строки «Январь».

Далее, на втором шаге итерации переменная «x» уже будет содержать Список {«Январь»}, а «y» — 2. Соответственно, формула (x, y)=> x & {Date.MonthName(#date(2019, y, 01))} примет вид:

({"Январь"}, 2)=> {"Январь"} & {Date.MonthName(#date(2019, 2, 01))}

Итогом второй итерации будет объединенный список {«Январь», «Февраль»}. И так далее со всеми остальными шагами итерации. Всего шагов будет 12 и итоговый список будет содержать наименования всех 12 месяцев:

 

Также, при помощи List.Accumulate можно формировать не только цифровые или текстовые значения, не только Списки, но и таблицы. В этом случае, во втором параметре должна быть пустая или с какими-то данными таблица, ну и пользовательская функция также должна возвращать строку из таблицы. Тогда на каждой итерации List.Accumulate будет в эту таблицу добавлять по одной строке.

 

На этом, с разбором функции List.Accumulate, входящую в состав редактора запросов Power Query (языка M) для Excel (Эксель) и Power BI, все. Успехов Вам!
 

Также, напоминаю Вам, в рамках распродажи до 30 апреля 2024 г. у Вас имеется шикарная возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 50% (вместо 10000, всего за 5000 руб.)

В этом видеокурсе язык DAX преподнесен как простой конструктор, состоящий из нескольких блоков, которые имеют свое определенное, конкретное предназначение. Сочетая различными способами эти блоки, Вы, при помощи конструктора формул DAX, с легкостью сможете решать любые (простые или сложные) аналитические задачи.

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 50% (до 30 апреля 2024 г.): узнать подробнее

До конца распродажи осталось:


 
 
Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»
 
 
 
 

Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.
 
Также, делитесь данной статьей со своими знакомыми в социальных сетях, возможно, этот материал кому-то будет очень полезен.

 
Понравился материал статьи?
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D

до конца распродажи осталось: