I thought it’d be interesting to find a way to highlight functions and operators in Power BI that you may haven’t come across before, deeply buried in the menus, or those that you may have seen, but don’t really know what they do. In this tutorial, I’d like to highlight the Power BI Modulo and Integer-Divide functions. Let’s take a look at how these functions can be useful to your reports. You can watch the full video of this tutorial at the bottom of this blog.
The Modulo and Integer-Divide in the standard operator within Power Query are incredibly useful for different types of data transformation. For demonstration purposes, let’s look at a simple division problem, fourteen divided by three. Four here, which is the whole portion of the result, is the Integer-Divide, while the Modulo is the remainder.
You can use Modulo and Integer-Divide to create different types of repeating patterns. And in those repeating patterns, you can do some really powerful transformations.
Using Power BI Modulo
So, let’s look at this case. We’ve got a simple data set, which is just twenty-four participants in a training course, and we want to take these participants in and divide them into four equal teams of six. We can use Integer-Divide and Modulo to do that in different ways. Let’s start with Modulo.
Let’s first add an Index column, and then in that column, let’s go and operate with Modulo.
In Modulo, we enter the number of groups that we want to create, which is 4.
You’ll see that it creates a repeating pattern of 0, 1, 2, and 3. Looking at 4 on the Index, which is actually the fifth row (because Power Query is zero base Index), if we divide 4 by 4, it’s an Integer-Divide of 1 and a Modulo of 0 remainder. It creates this repeating pattern all the way down to the bottom of the data set.
And so, we want to take these rows from Modulo and translate those into columns. We’re going to pivot the data on Modulo. If we go to Transform and Pivot, we want to pivot on First Name and we want to make sure that it’s not aggregating. We don’t want it to count. We just want it to pivot the actual values.
And then, we can take these four headers and do a fill-down, which will make it look better and less messy. Then, we can just filter the nulls out and filter the duplicates.
We’re left with our nice four teams of six. Modulo divided the data by counting off by four.
We can also do a different way of dividing these teams up, where instead of counting off and dividing teams that way, we can just count off the first six. The way we’re going to do this is with Integer-Divide.
Using Integer-Divide
Using Integer-Divide follows much of the same logic, but it’s a slightly different pattern. Let’s start off and we’ll go add columns and create our Index. Using Integer-Divide follows much of the same logic. It’s a slightly different pattern. Let’s start off and we’ll go add columns and create our Index. And then from that, we’re going to go Integer-Divide.
In this case, instead of what we wanted to do with Modulo, which was the number of groups, we’ll have the number of people per group. So, we put here six, and we get exactly what we’d hoped, which is the four groups of six.
Now we want to take this Index column and transform that into a Modulo, matching the Integer-Divide.
And so, this now creates the repeating pattern that matches the count of the Integer-Divide.
Then, we take this and transform it by pivoting this in the same way: by going to Advanced Options and making sure we don’t aggregate the data. From here, it actually cleans up with a very simple Transpose.
Transpose takes columns, turns them into rows, and vice versa. And so, if we take this data and Transpose it, and we go to our headers and use verse rows headers, we’ve now got exactly the teams we had.
***** Related Links *****
Power Query M: Alternating 0-1 Pattern In Date Table
Unpivot And Pivot Basics In Power BI – Query Editor Review
Power BI Query Parameters: Optimizing Tables
Conclusion
These are two different ways that we can use the Power BI Modulo and Integer-Divide to transform our data. You’ll find that these techniques are really useful in a lot of different transformational patterns in terms of stack data and paired data. You can use these to unwind those configurations in ways very similar to what we’ve done here today.
I hope you found that helpful. Expect to learn more about these buried treasures in Power BI in the coming weeks, so you too can make the most of them.
All the best!
Brian
[youtube https://www.youtube.com/watch?v=ArcTJ7X1U7E&w=784&h=441]
