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

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.

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.

Table of Contents

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.

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.

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.

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.

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.

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).

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.

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.

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.

Conclusion

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.