Перейти к содержимому

Цикл в Power Query на основе функции List.Accumulate

Язык М Power Query в явном виде использует единственную управляющую структуру:

Привычные нам по другим языкам программирования циклы отсутствуют. Как указывает Ben Gribaudo, это ни в коей мере не говорит о незрелости языка М. Просто в функциональных языках (к которым относится М) эти задачи решаются иначе. И всё же в М есть обходные пути, позволяющие организовать циклы. В частности, можно использовать функцию List.Accumulate. Как часто бывает, официальная документация по функции оставляет желать лучшего:

Рис. 1. Сведения о функции List.Accumulate с сайта Microsoft

Скачать заметку в формате Word или pdf, примеры в архиве (внутри два файла Excel)

Чтобы проверить код примера 1, вставим его в редактор PQ:

Рис. 2. Пример работает верно

Листинг 1[1]

Для начала разберемся с понятиями начальное значение и аккумулятор (накопитель). В листинге 1 начальное значение = 0, а функция…

…является аккумулятором.

Состояние (state) – это накопленное значение функции аккумулятора. В нашем примере накопление начинается с нуля. Накопленное значение меняется после каждой итерации функции аккумулятора. Значение состояния будет увеличиваться на число, которое представлено текущим значением списка (current). Давайте рассмотрим шаги последовательно:

Рис. 4. Итерационная работа функции List.Accumulate

На первом шаге состояние равно нулю , а текущее значение = 1 (первый элемент в списке). На этом этапе Power Query реализует функцию аккумулятора и выполняет суммирование state + current. Сумма = 1 и это значение присваивается состоянию перед второй итерацией.

Типы данных

В качестве накопителя вы всегда должны использовать функцию с двумя аргументами. Начальное состояние должно иметь тот же тип, что и первый аргумент функции-накопителя, и тип результата.

Продемонстрируем это с помощью псевдокода:

Листинг 3

myList – это список, элементы которого имеют тип TYPE_X (например, запись, список, число или текст), а наше начальное состояние имеет тип TYPE_Y, мы должны определить нашу функцию-накопитель как функцию, которая получает аргумент состояния, тип которого TYPE_Y и текущий аргумент тип которого равен TYPE_X, и возвращает значение, тип которого равен TYPE_Y.

Если не всё понятно, не теряйтесь! Продолжим изучение примеров.

List.Accumulate как конвейер

Листинг 4

Функция вернет число 100, которое является максимальным в списке {1, 2, 100, -40, 5}.

Давайте усложним и вычислим одновременно максимальное и минимальное значение в одном «конвейере».

Листинг 5

Результат – запись: [min = -40, max = 100]

В этом примере, чтобы передать два числа в качестве состояния (начального значения), мы определили запись с ключами min и max. Функция накопления также вычисляет новую запись min и max в соответствии с предыдущими min и max и текущим значением в списке. Можно еще усложнить пример, чтобы выполнить более двух вычислений за один проход.

Следующая функция использует List.Accumulate для создания отобранного списка имен столбцов из первоначального списка имен столбцов на основе списка индексов столбцов.

Листинг 6

Первая строка кода…

… задает пустую таблицу из трех столбцов. Имена столбцов по умолчанию образуют список {«Column1», «Column2», «Column3»}.

Функция…

… принимает таблицу tbl и список columnID.

В соответствии с требованиями к синтаксису функции List.Accumulate начальное состояние…

… имеет тот же тип (список), что и первый аргумент функции-накопителя…

… и тип результата…

Функция-накопитель использует функцию List.Combine

… и объединяет начальное состояние state, представляющее собой пустой список {} с именами таблицы tbl, последовательно отбираемыми по индексам columnID по одному за раз с помощью функции Table.ColumnNames()

Рис. 5. Итерации функции SelectionColumn

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

Рекурсия

Используем List.Accumulate для расчета чисел Фибоначчи. Эти числа задаются линейным рекуррентным соотношением:

F0 = 0, F1 = 1, Fn = Fn–1 + Fn–2,

где n ≥ 2, n Z.

Листинг 7. Функция Fibonacci

Первые два условия if проверяют входное значение и возвращают нулевой и первый член последовательности Фибоначчи. Если n ≥ 2, начинает работать рекуррентная формула на основе List.Accumulate. Для функции List.Accumulate нельзя определить условие динамического разрыва (выхода из цикла), поэтому нужно задать «правильный» список числа итераций:

… а функция List.Accumulate переберет в списке все элементы. Мы инициализируем список числами от 0 до input-2. Обратите внимание, что числа из списка {0..input-2} (они же, текущие значения) не участвуют в расчетах функции List.Accumulate. Список нужен лишь для определения числа итераций до останова. Расчеты же основаны на записи [PreviousNum = 0, CurrentNum = 1].

Эта запись определяет начальное значение и состоит из двух числовых полей: PreviousNum и CurrentNum. Функция-аккумулятор возвращает обновленную версию этой записи. Новое значение PreviousNum равно предыдущему значению CurrentNum. Новое значение CurrentNum равно сумме предыдущих значений PreviousNum и CurrentNum.

Например, 11-й член ряда Фибоначчи вычисляется за 10 итераций:

Рис. 6. Итерации функции Fibonacci

Функция Fibonacci в листинге 7 имеет следующий интерфейс:

Рис. 7. Вызов функции Fibonacci

Чтобы рассчитать значение любого члена ряда, просто введите его порядковый номер в поле input.

Листинг 8

Переименование столбцов по их позиции в таблице

В следующем примере я импортирую данные из файла fplreview.csv (приложен), который получаю из Интернета. Это прогноз выступления игроков Fantasy Premier League:

Рис. 8. Исходные данные (показаны не все столбцы)

Проблема в том, что с переходом к следующему туру некоторые заголовки столбцов (подчеркнул на рис. 8) меняются. Поскольку в коде PQ названия заголовков прописаны жестко, приходилось править код. Более того, поскольку я использую прогноз на восемь туров, нужно вносить изменения в код PQ, когда чемпионат подходит к концу и остается менее восьми туров. Я задумался, нельзя ли сделать запрос более робастным, чтобы его не приходилось править от тура к туру!?

И я добавил параметр – tour, который считывается из таблицы на листе Excel:

Рис. 9. Маленькая таблица параметров

Обязательно сохраните имя таблицы – Parameters. Оно жестко прописано в коде. Я также использую параметр fullfilepath, основанный на первой строк таблицы. Благодаря этому достаточно разместить файл fplreview.csv в той же папке, что Excel-файл с запросом, и ссылка на fplreview.csv обновится автоматически.

Листинг 9

Фрагмент…

… формирует названия переменных столбцов. Рассмотрим пример для tour = 3

Рис. 10. Итерации функции List.Accumulate

Код…

… сначала превращает таблицу в список столбцов (функция Table.ToColumns); при этом теряются заголовки столбцов, а затем (функция Table.FromColumns) – обратно в таблицу, давая столбцам новые заголовки (newClmnNames).

Множественный поиск и замена

Если у вас есть текст, и нужно заменить несколько слов (подстрок), поместим текст в умную таблицу на лист Excel (mytext, рис. 11а), а слова для поиска и замены в таблицу replacements (рис. 11б).

Рис. 11. Исходные данные для множественного поиска и замены: а) текст для поиска; б) таблица подстановки

Листинг 10

Сначала мы загрузили две таблицы и изменили тип их столбцов на text. Далее добавили пользовательский столбец After replacement, заполнив его текстом на основе функции List.Accumulate. Внутри этой функции создали список чисел, начиная с 0. Размер списка равен количеству строк в таблице подстановки – {0,1,2,3,4}. В качестве начального состояния взяли столбец Text таблицы mytext. Итерации начинаются со значения current = 0. Берется значение из первой строки столбца Find таблицы replacementsfour и заменяется на значение из первой строки столбца Replacesix. Перед второй итерацией новое состояние аргумента state содержит исходный текст с заменными значениями four –> six. Аргумент current получает значение 1, и происходит замена normal –> casual. После пяти замен работа List.Accumulate завершается. Текст после этих замен помещается в соответствующие строки столбца After replacement.

Удаление пустых строк и столбцов из таблицы

Допустим, у вас есть неказистая таблица:

Рис. 12. Исходная таблица, содержащая пустые строки и столбцы

Задача – удалить полностью пустые строки и столбцы. На рис. 12 они выделены желтым. Если загрузить таблицу в PQ, то пустые строки можно удалить командой Главная –> Удалить стоки –> Удалить пустые стоки. К сожалению в PQ нет команды удалить пустые столбцы… Но, можно транспонировать таблицу, выполнить команду Удалить пустые стоки и повторно транспонировать в исходный вид. Эту же задачу можно решить с помощью функции List.Accumulate:

Листинг 11

Цикл на основе функции List.Accumulate

Итак, функция List.Accumulate позволяет организовать цикл. Функция List.Accumulate имеет три аргумента: list (список), seed (начальное значение) и accumulator (функция-аккумулятор).

list может содержать:

  • элементы, которые участвуют в работе функции-аккумулятора (листинг 1, 4, 5, 6, 11),
  • индексы, которые лишь задают число итераций функции-аккумулятора (листинг 7, 9, 10),

seed может быть:

  • числом (листинг 1, 4), текстом, иным значением
  • записью (листинг 5, 7), списком (листинг 6, 9), таблицей (листинг 10, 11).

accumulator – функция с двумя аргументами.

Использованные материалы

Gil Raviv. Power Query List.Accumulate – Unleashed

Gil Raviv. Automatically Remove Empty Columns And Rows From A Table In Power BI

Gil Raviv. Book Excerpt: Loops, Recursions, Fibonacci And More From Chapter 9: M #PowerQuery

Chris Webb. Converting Lists Of Numbers To Text Ranges In Power Query

Purna Duggirala. Multiple Find Replace with Power Query List.Accumulate()

Ken Puls. Running Totals using the List.Accumulate() Function

[1] Номер листинга соответствует номеру запроса в приложенном Excel файле.

Добавить комментарий

Ваш адрес email не будет опубликован.