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.
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.
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.
The image above shows column A as a random number from 0 to 1.
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.
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.
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.
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.
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()
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()
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()
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.
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.
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.
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.
The output of rounding [WeightTons] is shown as column H.
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.
While I think the Power Query formula language is very powerful, the desktop designer leaves the developer with a bunch of funny named steps.
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.
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.
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
- Read the first tip in the series: Introduction to the Power BI Desktop and the M Language
- Stay tuned for more tips on the M Language
