Power Query M: Alternating 0-1 Pattern In Date Table

by | Power BI

This tutorial focuses on using the Power Query M number.mod function to create an alternating 0-1 pattern in the Date table. The solution that I’m going to show you came from one of our community power users, Rajesh, at Enterprise DNA. You may watch the full video of this tutorial at the bottom of this blog.

The aim is to generate a sequence of four zeros alternated by four ones, and then repeat it throughout the day.

power query m

Rajesh was spot on with his analysis and problem breakdown using the Power Query M function to solve it. With that said, let’s go over to Power Query.

The Power Query M Number.Mod Function

The Power Query M, specifically the number.mod function is going to be the core of our solution. What does it do?

You can check the internal documentation of any M function by entering its name without parentheses as shown here in the formula bar. This function returns the remainder of an integer division. It takes up to three parameters, the first two, a number, and the divisor, which is also a number.

power query m

Now let’s get started. I’ll select my sample query that contains a date range that I’ve prepared.

In many cases, number.mod is used in combination with an index column that’s then used as its first parameter. But I’m not going to do that because if you think about it, the date value is an integer. So creating an index that shares the same granularity as the Date column, where each row has a unique value is something that we can avoid.

All we have to do to mimic an index is subtract the very first date in our Date table from each date in the Date column. To do that, we can store the start date value in a variable so we can use that over and over again. I find the Advanced Editor is the best place to do this.

So I’ll open the Advanced Editor window and create a new variable. Let’s call it StartDate. I hard-coded my First Date here so I’m just going to copy and paste it in. Don’t forget the comma at the end, and we can now replace that First Date with our Start Date variable.

power query m

As you can see, nothing changed.

Creating Custom Column For The Power Query M Pattern

So via this mini table, we’re going to add a custom column.

Let’s call this column, Pattern, and enter the Power Query M function, number.mod. You see the two parameters here, a number as a nullable number and a divisor as a nullable number. So it requires two number type values as parameters.

power query m

Now we don’t have numbers, right? We have dates and there’s no such thing as automatic type conversion in them. So we need to get the number from our date value. So we type in here, number.from then our Date column and then subtract our StartDate variable.

power query m

Now, let’s say the first date in our Date table returns a one, then our StartDate will also return a one. And so, one minus one equals zero. I want our index numbering to start with a one instead of a zero. So we’ll have to add one back plus one. Now one minus one equals zero plus one returns a one for the first record.

power query m

For the second record, we get a two (2) value for the date in the Date column, minus a one (1) from our StartDate value that equals one, plus the one we’re adding back returns a two, and so on. Our repeating pattern was based on a series of four alternating values. So as a divisor, I’m going to pass in the value four (4).

power query m

Let’s see what that does. In row four, we get zero, as well as in row eight. So each fourth occurrence returns a zero value for that record.

Creating An IF-THEN-ELSE Construct

Let’s go back to our customer dialogue box and extend on the logic we’ve written so far. Except for the divisor, we need to repeat the same logic to be able to identify an eighth occurrence.

However, on that eighth row, we want a different value. So to make a distinction between a fourth and an eighth occurrence, we can use an IF-THEN-ELSE construct. That way, we can return a value for the first logical test that returns a true.

So If, and I’ll paste back our code here (except for the divisor), change that divisor into an eight so that way an eighth row will return zero. So if that equals zero, that means it will fail your way to true or false. Then we want a one (1). Else, if our logic here identifies a fourth row equals zero, then we want zero (0). Else we don’t want to get a value back. So let’s add a null.

power query m

Now, let’s check our logic again. So on each fourth occurrence, we get that zero. And then, on the eighth, we get a one. Next, we get zero and a one, and so on.

So this is looking good. All we have to do now is fill up these values.

power query m

So inside the formula bar, we’ll add a Table.FillUp at the beginning then some opening parentheses. At the end of our code, we add type number – we also need to add a column as a list. So, we go comma, then use the curly brackets for list initializer and as a text value pass in our column name, Pattern. Don’t forget the closing parentheses and press OK.

***** Related Links *****
Beginners’ Guide To The M Code In Power BI
M Function For Date Table – How To Add A Parameter In The Query Editor
Sorting Date Table Columns In Power BI


This is a very unique scenario, and honestly, I can’t think of an application for this specific pattern, but hopefully you can see the potential of creating sequences using the Power Query M function, number.mod.

For more details, watch the full video below for this tutorial.

All the best!


[youtube https://www.youtube.com/watch?v=DvvP1l0TLBA?rel=0&w=784&h=441]

Related Posts