Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

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

by | 7:00 pm EST | March 10, 2021 | 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.

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.

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

All the best!

Melissa

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

## Can Excel Open JSON? A Quick How-To Guide

Yes, Excel can open JSON files, with the latest versions of Excel being even more capable. By using...

## Javascript Translator: Quickly Translate Your Code

In recent years, JavaScript has become the go-to programming language for developers due to its...

## Bash To Powershell Converter: Quick & Easy Guide

In today's diverse IT landscape, seamlessly transitioning between Unix/Linux and Windows environments...

## 33 Important Data Science Manager Interview Questions

As an aspiring data science manager, you might wonder about the interview questions you'll face. We get...

## Programming Language Detector: Using ChatGPT

Which programming language is this??? Don't worry; we've all been there at some point! The good news is...

## Data Science Case Study Interview: Your Guide to Success

Ready to crush your next data science interview? Well, you're in the right place. This type of...

## Code Paraphrase Tool: The Ultimate Guide

As developers, we are always on the lookout for new tools that can improve our coding efficiency and...

## Top 22 Data Analyst Behavioural Interview Questions & Answers

Data analyst behavioral interviews can be a valuable tool for hiring managers to assess your skills,...

## Javascript Auto Clicker: Explained with Examples

Have you ever needed to repeat a specific action repeatedly in a web application but found it too...

## Big O Calculator: Top Solution for Big O Notation Creation

When writing efficient code, the Big O notation is a programming concept that can help you measure your...

## Javascript to Java Converter: Quick, Efficient & Accurate

So, you want to convert Javascript to Java? Well, you're in luck. In recent years, artificial...