Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Rounding Numbers using the M Language


By:   |   Read Comments   |   Related Tips: More > Power BI

Problem

The Power BI desktop designer is a great tool; however, the hardest part of any Business Intelligence project is gathering and formatting the data into reportable datasets. In this tip we look at the rounding functions that are available using the M language.

Solution

Just recently, Microsoft has release its latest Power BI desktop designer from public preview to general release in July 2015. The designer can be downloaded from the following link.

The designer is based upon Power Query to import data into the model. The x-velocity engine is used to save the data in a space saving format. The imported datasets can be related via relationships. The report visualizations can be categorized into different dashboard pages. This part of the designer is based upon Power View.

If you have worked with Power Query and Power view in the past, you will be right at home.

The data section of the Power BI designer is where you import and manipulate data. Just like SQL Server Management Studio which obscures the TSQL generated by menu selections and actions, the Power BI designer is based upon the Power Query formula language informally know as "M".

Having a deeper understanding of the M language will help a BI developer in cases where the menus can not.

Rounding Numbers with Power BI Desktop

Today's business problem is to learn how to round numbers using custom (computed) columns. We will continue using the CARS dataset introduced in the last article.

Setup Rounding Examples

First, open the Power BI project you saved from the last time. To re-enter the query designer, select the data tab. Choose the home menu and the edit existing query option from the tool bar ribbon.

Add a custom column in Power BI Desktop Edition

Second, choose the add column menu and add custom column option on the tool bar ribbon. If you did everything correctly, your screen should look like the one above.

I will be naming the custom columns after letters in the ALPHABET. This way, we will know these columns are for demonstration purposes only.

Use the Random Number function in Power BI Desktop Edition

There are five rounding functions in the M language. Before we can use these functions, we need to have both positive and negative numbers to work with. I am going to select the [Acceleration] column as the source of the data.

The simplest algorithm is to use a built in function to randomly make rows negative or positive. There are two random functions in the M Language. The Number.Round() function allows the BI developer to define a column with a return number between 0 and 1.

Number.Round function in Power BI Desktop Edition

The image above shows column A as a random number from 0 to 1.

Number.RandomBetween function in Power BI Desktop Edition

The Number.RoundBetween() function allows the BI developer to define a column with a return number between X and Y. Values for X and Y are values supplied by the developer and are passed to the function. The image below shows column B as a number between 0 and 10.

Power BI Desktop Edition Number.RoundBetween() Function

Okay, that is an unexpected result. It seems like there is a bug in the M language. More than one call to a random function with a static input value results in the same output. Do not fret yet. We can define a column using the [Acceleration] field as input.

Custom Column with IF THEN ELSE logic in Power BI Desktop Edition

I am going to introduce the "if then else" expression in the language. The new formula generates a number from zero to the [Acceleration] field. If the number is less than 1/2 of original number, turn the number negative; Otherwise, save the number as a positive number.

Data set for IF THEN ELSE logic Power BI Desktop Edition

1 - Number.RoundDown()

That was not too much work to create a column C that has [Acceleration] expressed as random positive or negative numbers. Now, we are finally ready to start talking about rounding functions.

Number.RoundDown function in Power BI Desktop Edition

The Number.RoundDown() function allows the BI developer to define a column with input field rounded down to the next lowest integer. Column D uses this function with column C as input.

2 - Number.RoundUp()

Number.RoundUp function in Power BI Desktop Edition

The Number.RoundUp() function allows the BI developer to define a column with input field rounded up to the next highest integer. Column E uses this function with column C as input.

3 - Number.RoundTowardZero()

Number.RoundTowardZero function in Power BI Desktop Edition

The Number.RoundTowardZero() function allows the BI developer to define a column with input field rounded towards zero using the following rules: if input is negative, round the number up and if input is positive, round the number down. Column F uses this function with column C as input.

4 - Number.RoundAwayFromZero()

Number.RoundAwayFromZero function in Power BI Desktop Edition

The Number.RoundAwayFromZero() function allows the BI developer to define a column with input field rounded away from zero using the following rules: if input is negative, round the number down and if input is positive, round the number up. Column G uses this function with column C as input.

Number.RoundAwayFromZero data set in Power BI Desktop Edition

5 - Number.Round()

So far we have covered 4 out of 5 of the rounding functions. Does the image above help you visualize how the functions work? One thing that is missing from these functions is the ability to define precision.

Custom Column with Weight Divided By 2000 in Power BI Desktop Edition

I am going to create a custom column named [WeightTons] which is defined as the car weight in pounds divided by 2000. This will give us a number with three decimal points.

Weight in Tons calculation in Power BI Desktop Edition

The Number.Round() function allows the BI developer to define a column with input field rounded to Z decimal places. I consider this function a super function since the third argument is the rounding algorithm. This argument is optional and defaults to next integer number using RoundUp or RoundDown algorithms.

Number.Round function with a dynamic rounding algorithm in Power BI Desktop Edition

The output of rounding [WeightTons] is shown as column H.

Power BI Ribbon Rounding Options

Power BI Ribbon Rounding Options

It is interesting to note that we can find the rounding button under the transform ribbon. It only exposes 3 of the 5 functions.

Rounding Functions in M Language

We spent a-lot of time examining random and rounding functions in the M language. If we examine the generated code under the advance editor button, we can see each query steps expressed in the M language.

Advanced Editor for Rounding Functions in M Language

While I think the Power Query formula language is very powerful, the desktop designer leaves the developer with a bunch of funny named steps.

Applied Steps in Power BI Desktop Edition

The above image shows the query steps with the name given by the designer. You can right click each step and rename them. Better yet, you can be like me and open the advance editor and make changes.

Power BI Desktop Edition Advanced Editor with C++

The M Language supports C++ comments. The tricky part in renaming the steps is that both the current step and the next step need to be modified. If you are lucky, your code will look like mine above. If are having issues, use the right click method.

Final Applied Steps in M Language

The fruits of our labor are shown in the image above. We can see each step now has a natural language description.

Summary

The Power BI desktop designer is a FREE and powerful tool that will allow an end user to quickly import data into the model. Many standard manipulations can be selected from the menus as query steps. You can see how the data changes from one step to another. This is very helpful in debugging your work.

The introduction of custom (computed) columns allows the BI Developer to use the full M language. The random functions are great for generating test data. The rounding functions allow the developer to provide the end user reports with estimates instead of exact decimal numbers. There are many other languages elements such as comments that can be used to document your work.

Next Steps


Last Update:






About the author
MSSQLTips author John Miner John Miner is currently a Microsoft Technology Solutions Professional (TSP) advising North East District (NED) corporations.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools