Power Query M: чередующийся шаблон 0-1 в таблице дат

В этом руководстве основное внимание уделяется использованию функции Power Query M number.mod для создания чередующегося шаблона 0-1 в таблице дат. Решение, которое я вам покажу, пришло от одного из опытных пользователей нашего сообщества, Раджеша, из LuckyTemplates. Вы можете посмотреть полное видео этого урока в нижней части этого блога.

Цель состоит в том, чтобы сгенерировать последовательность из четырех нулей, чередующихся с четырьмя единицами, а затем повторять ее в течение дня.

Раджеш был точен в своем анализе и анализе проблемы, используя для ее решения функцию Power Query M. С учетом сказанного давайте перейдем к Power Query.

Оглавление

• Функция Power Query M Number.Mod
• Создание пользовательского столбца для шаблона Power Query M
• Создание конструкции IF-THEN-ELSE
• Заключение

Функция Power Query M Number.Mod

Power Query M, в частности функция number.mod, станет ядром нашего решения. Что оно делает?

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

Теперь давайте начнем. Я выберу свой пример запроса, который содержит подготовленный мной диапазон дат.

Во многих случаях number.mod используется в сочетании с индексным столбцом, который затем используется в качестве его первого параметра. Но я не собираюсь этого делать, потому что, если подумать, значение даты является целым числом. Таким образом, мы можем избежать создания индекса с той же степенью детализации, что и столбец Date, где каждая строка имеет уникальное значение.

Все, что нам нужно сделать, чтобы имитировать индекс, — это вычесть самую первую дату в нашей таблице Date из каждой даты в столбце Date. Для этого мы можем сохранить значение даты начала в переменной, чтобы мы могли использовать ее снова и снова. Я считаю, что Advanced Editor — лучшее место для этого.

Итак, я открою окно расширенного редактора и создам новую переменную. Назовем его StartDate . Я жестко запрограммировал свое первое свидание здесь, поэтому я просто скопирую и вставлю его. Не забудьте запятую в конце, и теперь мы можем заменить это первое свидание нашей переменной даты начала.

Как видите, ничего не изменилось.

Создание пользовательского столбца для шаблона Power Query M

Итак, с помощью этой мини-таблицы мы собираемся добавить пользовательский столбец.

Давайте назовем этот столбец Pattern и войдем в функцию Power Query M, number.mod . Здесь вы видите два параметра: число как число, допускающее значение NULL, и делитель, как число, допускающее значение NULL. Поэтому в качестве параметров требуется два числовых значения.

Теперь у нас нет номеров, верно? У нас есть даты, и в них нет такого понятия, как автоматическое преобразование типов. Итак, нам нужно получить число из нашего значения даты. Итак, мы вводим здесь number.from , затем наш столбец Date , а затем вычитаем нашу переменную StartDate.

Теперь предположим, что первая дата в нашей таблице Date возвращает единицу, тогда наша StartDate также вернет единицу. Итак, один минус один равно нулю. Я хочу, чтобы наша нумерация индексов начиналась с единицы, а не с нуля. Так что нам придется добавить один назад плюс один. Теперь один минус один равно нулю плюс один возвращает единицу для первой записи.

Для второй записи мы получаем значение два (2) для даты в столбце Дата минус один (1) из нашего значения StartDate, которое равно единице, плюс тот, который мы добавляем обратно, возвращает два и т. д. . Наш повторяющийся шаблон был основан на серии из четырех чередующихся значений. Итак, в качестве делителя я передам значение четыре (4).

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

Создание конструкции IF-THEN-ELSE

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

Однако в этой восьмой строке нам нужно другое значение. Таким образом, чтобы провести различие между четвертым и восьмым вхождением, мы можем использовать конструкцию IF-THEN-ELSE. Таким образом, мы можем вернуть значение для первого логического теста, который возвращает истину.

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

Теперь давайте еще раз проверим нашу логику. Таким образом, в каждом четвертом случае мы получаем этот ноль. А потом, на восьмой, мы получаем единицу. Далее получаем ноль и единицу и так далее.

Так что это выглядит хорошо. Все, что нам нужно сделать сейчас, это заполнить эти значения.

Итак, внутри строки формул мы добавим сначала Table.FillUp , а затем несколько открывающих скобок. В конце нашего кода мы добавляем номер типа — нам также нужно добавить столбец в виде списка. Итак, мы ставим запятую , затем используем фигурные скобки для инициализации списка и в качестве текстового значения передаем имя нашего столбца Pattern . Не забудьте закрывающие скобки и нажмите OK.

Руководство для начинающих по коду M в LuckyTemplates
Функция M для таблицы дат — как добавить параметр в редактор запросов
Сортировка столбцов таблицы дат в LuckyTemplates

Заключение

Это очень уникальный сценарий, и, честно говоря, я не могу придумать приложение для этого конкретного шаблона, но, надеюсь, вы видите потенциал создания последовательностей с помощью функции Power Query M, number.mod.

Для получения более подробной информации, посмотрите полное видео ниже для этого урока.

Всего наилучшего!

Мелисса