Power Query M: змінний шаблон 0-1 у таблиці дат

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

Мета полягає в тому, щоб створити послідовність із чотирьох нулів, які чергуються з чотирма одиницями, а потім повторювати її протягом дня.

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

Зміст

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

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

TheM, зокрема функція number.mod буде ядром нашого рішення. Що воно робить?

Ви можете переглянути внутрішню документацію будь-якої функції M, ввівши її назву без дужок, як показано тут у рядку формул. Ця функція повертає залишок від цілочисельного ділення. Він приймає до трьох параметрів: перші два, число та дільник, який також є числом.

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

У багатьох випадках number.mod використовується в поєднанні зі стовпцем індексу, який потім використовується як його перший параметр. Але я не збираюся цього робити, тому що якщо ви подумаєте про це, значення дати є цілим числом. Отже, ми можемо уникнути створення індексу з такою ж деталізацією, що й стовпець Date, де кожен рядок має унікальне значення.

Все, що нам потрібно зробити, щоб імітувати індекс, це відняти найпершу дату в нашій таблиці Date від кожної дати в стовпці Date. Для цього ми можемо зберегти значення дати початку у змінній, щоб використовувати її знову і знову. Я вважаю, що розширений редактор є найкращим місцем для цього.

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

Як бачите, нічого не змінилося.

Створення спеціального стовпця для шаблону Power Query M

Отже, за допомогою цієї міні-таблиці ми збираємося додати спеціальний стовпець.

Давайте назвемо цей стовпець Pattern і введемо функцію Power Query M number.mod . Тут ви бачите два параметри: число як число, яке допускає обнулення, і дільник як число, яке може обнулюватися. Отже, як параметри потрібні два значення числового типу.

Тепер у нас немає цифр, чи не так? У нас є дати, і в них немає такого поняття, як автоматичне перетворення типів. Отже, нам потрібно отримати число з нашого значення дати. Отже, ми вводимо тут число.звідси наш стовпець Date , а потім віднімаємо змінну StartDate .

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

Для другого запису ми отримуємо два (2) значення для дати в стовпці Date, мінус один (1) із нашого значення StartDate, яке дорівнює одиниці, плюс той, який ми додаємо назад, повертає двійку, і так далі . Наш повторюваний шаблон базувався на серії з чотирьох змінних значень. Тож як дільник я збираюся передати значення чотири (4).

Давайте подивимося, що це дасть. У четвертому ряду отримуємо нуль, як і у восьмому. Отже, кожен четвертий випадок повертає нульове значення для цього запису.

Створення конструкції IF-THEN-ELSE

Давайте повернемося до нашого діалогового вікна клієнта та розширимо логіку, яку ми написали досі. За винятком дільника, нам потрібно повторити ту саму логіку, щоб мати змогу ідентифікувати восьмий випадок.

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

Отже , якщо , і я вставлю сюди наш код (за винятком дільника), змініть цей дільник на вісімку, щоб восьмий рядок повертав нуль. Отже, якщо це дорівнює нулю, це означає, що він не пройде ваш шлях до істинного чи хибного. Тоді ми хочемо один (1). Інакше , якщо наша логіка визначає, що четвертий рядок дорівнює нулю, тоді нам потрібен нуль (0). Інакше ми не хочемо отримати вартість назад. Отже, давайте додамо null .

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

Так що це виглядає добре. Все, що нам зараз потрібно зробити, це заповнити ці значення.

Тож у рядок формул ми додамо Table.FillUp на початку, а потім кілька відкриваючих дужок. У кінці нашого коду ми додаємо номер типу – нам також потрібно додати стовпець як список. Отже, ми використовуємо кому , потім використовуємо фігурні дужки для ініціалізатора списку та як текстове значення передаємо назву нашого стовпця, Pattern . Не забудьте про круглі дужки та натисніть OK.

Висновок

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

Щоб дізнатися більше, перегляньте повне відео нижче для цього підручника.

Все найкраще!

Меліса