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. For 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 transformations. 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 on 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 make sure that it’s not aggregating. We don’t want it to count or anything. We just want it to pivot the actual values.
And then, we can take these four headers and we do a fill down, it’ll look better and less messy. Then, we can just filter the nulls out and filter the duplicates.
And we’re left with our nice four teams of six. Modulo divided the data up 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 follows much 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 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, now this creates the repeating pattern that matches the count of the Integer-Divide.
Then, we take this and transform by pivoting this in the same way, by going to Advanced Options and making sure that we don’t aggregate the data. From here, it actually cleans up with a very simple Transpose.
Transpose just 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 just use verse rows headers, we’ve now got exactly the teams we had.
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 have more of these buried treasures in Power BI that you can use in your own work in the coming weeks.
All the best!
***** Related Support Forum Posts *****
Column with a Pattern
Table Transformation, Removing Duplicates from KeyColum
Power BI Accelerator Week #8 is Live!
For more modulo support queries to review see here….