Automatically Round Off Number Columns with Power Query

I was working on additional functions for the Power Query Shared Library and thought about routine things that many people may do to set up tables. If you have a large table (large amount of rows and potentially columns), this can make your report slower if you are not careful. One complicating factor is needless granularity.

What do I mean by needless granularity? It is essentially having values at a specificity that adds no value to the end user that consumes the report. One example is having DateTime values down to the second when the measures are aggregated by day. Another example is having 10 decimal places, when 2-4 would do just fine and would not take away from the insights.

Why does this even matter? In simple terms, it boils down to encoding and compression. The more unique values in each column of a long table, the more hurdles Power BI will have to deal with when trying to compress. Better compression helps DAX shine (as well as other things).

I already had a function to automatically convert DateTime columns to date, but I did not have anything to deal with rounding number columns to a more manageable decimal place. Until now.

The Function

Here is a link to the code itself in Github.

Using Table.Schema was an easy way to automatically select the number columns. After that, it took a little to figure out how to round each column once they were selected. Table.TransformColumns takes a list of column and transformation pairs, which is the route I took. The key part was using List.Repeat with a function that rounded off the numbers in the column (using Number.RoundAwayFromZero, not Banker’s rounding – for a good discussion on the difference, see Ken Puls’s blog about this).

Using the Function

Taking three parameters (one optional), invoking it is as simple as passing in a table and how many decimals places you need in the output. Additionally, if there are any columns that you want to exclude, use the third parameter and pass in the names of the columns that you want to exclude as a list.

Rounding an example table to 2 decimal places:

Excluding Column B:

Make sure to set your data types before invoking this or else Table.Schema will not pick up on all of the columns that may need to be rounded.

Wrap Up

I think this is a pretty handy one. Again, if you do not need a high degree of accuracy and specificity for your report, then don’t load that into your model. The best place to take care of things like that is at the source, but if you cannot do it there, then a function like this in Power Query could be the way to go.