# Power BI Modulo and Integer-Divide DAX Functions

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.

## 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

Brian Julius
Brian Julius has over 30 years experience in performing complex analysis of economic and business operations data. His interests include addressing DAX-related challenges and applying innovative techniques to improve the Power BI user experience.

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## Calculate Rolling Totals Using DATESBETWEEN In Power BI

Here Iâ€™m going to show you how you can calculate the amount sold between two different dates using the...

## Formatting DAX Code In Power BI

Effort here will pay dividends later Placing effort into formatting your DAX code will make a huge...

## Cumulative Totals Based On Monthly Average Results In Power BI

Today, I wanted to cover a unique technique around cumulative totals based on monthly average results...

## Power Automate Expressions – An Introduction

In this tutorial, weâ€™ll explore Power Automate expressions and how to use them in workflow automation....

## List.Max Power Query: User Guide With Examples

One of the most powerful and useful functions in Power Query M language is List.Max. It can be...

## R Scripting For Power BI Using RStudio

In this blog, weâ€™re going to go through the basics of R scripting for Power BI using RStudio. The...

## Simple Aggregations In Power BI

In this blog post, I'll touch on some simple aggregations in Power BI that you can use in your DAX...

## Microsoft Report Builder: Steps In Creating A List

In this tutorial, you will learn how to create a list and page break in Microsoft Report Builder. A...

## 5 Strategies To Enhance Your Power BI DAX Skills

In this tutorial, youâ€™ll learn the general strategies to improve your DAX or M capabilities. This is an...

## ALL Function in Power BI – How To Use It With DAX

Did you know that the ALL function can be used to modify the context of a particular calculation in...

## Calculations In Power BI Using Measure Branching

Measure Branching is a technique in making calculations in Power BI. It's not something you'll hear...