Power BI – Modulo and Integer-Divide Function

By: David Rohlfs

Introduction

Often when you are working with Power BI you start working with complex code and transformations in the Power Query. And you often do this so much that you forget some of the basic aggregations and transformations. In a previous blog I went over Arithmetic in Power Query (linked below). In that post I did not go into detail on any specific function, but the Modulo and Integer-Divide function merit some explanation.

In this blog we are going to review what the Modulo and Integer Divide functions are and go through a short demonstration of how these functions work.

There are many great uses that you can find for these functions on the internet, but this blog is just going to describe the basics.

 

When to use it

It is hard to answer the question of when you should use these functions because they can be molded to so many different scenarios. So, in this section I am going to focus on what these functions are. The Integer-Divide and Modulo functions are complimentary to each other, and they both go back to a basic math divide equation.

To demonstrate these two functions, I want you to think about dividing the number 325 by 100. The answer is an obvious 3.25, but what if you want to separate out this 3.25 to its two parts? You can do the whole number (3) and the remainder (25), and this is what the Modulo and Integer-Divide function do. The Modulo function represents the remainder amount (25) while the Integer-Divide function represents the whole number amount (3).

This is another example where the Volume column is being divided by 200 using the Modulo and Integer-Divide functions:

Now that we know what these functions do, why would we use them? There aren’t a lot of obvious use for the Modulo and Integer-Divide functions. A lot of the time when you are working with data you are doing these large complex transformations where math can be a small part of the process, but it is not the final process. If you start to look online there are a lot of really cool applications for both of these functions, my favorite being an example on grouping data by using the modulo and integer-divide function.

 

Walkthrough

In this walkthrough I am going to go through the two ways that you can use the Modulo or Integer-Divide functions in your data. I will be doing the Integer-Divide function on the original column, and the Modulo function by adding a new column. Know that you can do each of these functions on either the original or a new column. If you have already read the Arithmetic in Power Query blog this will be the same process as that.

To start you are going to need to go to the Power Query by clicking on the Transform Data button.

Now you should see your Power Query. In here make sure that you select your query on the left ribbon then select the column that you want to use the function on. Because this is an arithmetic function, you are going to need to use a column that has a numerical type to it. I am going to use my Volume column that has a whole number data type.

Now we can go to the Transform ribbon and go to the Number Column group, then select the dropdown for Standard and select the Integer-Divide function.

This will give you a pop-out where you can manually enter the value to divide by. I am going to use the amount 200.

Once you do that you can click on OK in the bottom right of your pop-out.

Now, you should see that your column has been divided by your amount with no remainders. Notice my third row is 0, this is because I could not divide my original value (100) by 200 without using a decimal.

Let’s try out the Modulo function on the Add Column ribbon. I am going to start with a new version of the same query that has not been altered by the integer-divide function for the demonstration.

Select the column that you want to change, for me it will be the volume function again, then go to the Add Column ribbon, the From Number group, Standard, and Modulo (this is the same process as before, but we are on the add column ribbon).

You should get a pop-out that looks like this. I am entering the value 200 in the line to give a similar demonstration as the Integer-Divide function, but you should know that you can use a column as your values.

Once I click OK in the bottom right corner of my pop-out, this is my result:

Notice that the third line is 100, this is the remainder when you divide that value by 200. This is complementary to the 0 that we got with the Integer-Divide function.

 

Conclusion

Although going through a couple of basic functions can be boring, it is still important to know their uses. Often when you work with data you get stuck on large complex equations as being a solution to a problem, but some of these smaller functions are there to save time with large sets of data. If you enjoyed this blog, I suggest that you read some of EPM Strategy’s other blogs linked below.

 

Links to Related Articles:

Group and Bin Data

Group By

Column From Examples

Arithmetic in Power Query

Comments