Learn more about SQL Server tools

 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Advanced Mathematical Formulas using the M Language


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

Problem

The Power BI desktop designer is a great tool and in this tip we will look at advanced mathematical formulas using the M language.

Solution

The first step of any Power BI project is to load data into the model. The image below explains the process flow for Power Query, but still applies to the Power BI desktop designer. Today, we will connect to our Excel data source. We will be writing advanced mathematical formulas in the M language. Since we are working with only one dataset, there will be no combining of data. As a result of our actions, the data is available (shared) for reporting.

Loading Sample Data

Find the home menu on the Power BI desktop designer. Click the Get Data option on the ribbon. Click more at the bottom of the drop down box of most common selections. Choose the FILE option on the left pane. Last but not least, select MS Excel as the import file type.

Get Data Options with XML

Browse to the location of the "advance-math.xls" file. This is a simple data set that I crafted for this tip. You can download the file from this link.

Open the advance-math.xls file

Clicking the OPEN button loads the data into a preview view window. Select the "Sample Data" worksheet as the source. The user can either LOAD the data without manipulation, EDIT the data or CANCEL the current operation.

Navigator to view sample data

Please choose the EDIT option to manipulate the data using a set of steps in a query.

We can see that the last column needs to be removed from the data set. Also, the first row needs to be promoted as a header. When it is possible, fixing data at the source will reduce the number of query steps. Each step can be equated to some amount of processing on your laptop. Thus, adding to the over time to load the data. In your Sample Data set, I already removed column 7.

EDIT option in the Power BI Desktop

Again, please promote the top most line to a header. Change the data type of the [ZeroSum], [Root] and [Power] columns to integers. Convert the data type of [Dividend] and [Divisor] columns to decimals. This task can be done by right clicking the column and selecting change type action from the menu. Choose the correct type in the sub-menu.

The sample data set contains the following columns: [IsNumber] contains alpha numeric data; [ZeroSum] contains negative, positive, odd and even numbers; [Dividend] & [Divisor] contain real numbers for division; and the [Root] & [Power] contain integer values.

We will be using this data with our advanced mathematical formulas.

Advanced Editor for mathematical formulas

If you followed the instructions correctly, the resulting M language query should look like the one above. The Excel.Workbook function tells the M language the content type of the file. Since there can be multiple worksheet in any MS Excel file, the Source function selects the correct data.

Data Type Conversions in Power BI

Many times, a BI Developer needs to convert from a textual format to a numerical format. How can we accomplish this task?

Add a Custom Column for Data Type Conversions

The Number.From function allows for the conversion of a text value to a numeric value.

Number.From function to convert text to numeric

However, the results are just not appealing. Any value that is a number is correctly converted to a number. All null values result in a null value result. Any non-numeric characters in the input results in a error value. In this format, we can not apply any aggregations during reporting.

Code with TRY and OTHERWISE clauses

Like most languages, the M language has implemented error handling. Start the computed column with the try clause and catch errors with the otherwise clause. To allow for aggregation, we will convert the data to a number. If there is an error during conversion, we will return a zero value.

Data set with TRY and OTHERWISE clauses

The [Convert2Number] column now contains data that we can report on. I just touched the tip of the iceberg when it comes to these functions.

Numerical Formulas in Power Query Formula Language

The Power Query formula language informally know as "M" supplies the developer with a bunch of functions on information and operations involving numerical data. Let us start looking at the informational functions using the [ZeroSum] column. We will be adding computed columns to our Sample Data set to demonstrate each function.

M Language Number.IsOdd Function

We can determine if a number is odd by calling the Number.IsOdd function.

Sample code for the Number.IsOdd function

M Language Number.IsEven Function

We can determine if a number is even by calling the Number.IsEven function.

Sample code for the Number.IsEven function

M Language Number.Abs Function

We can determine the magnitude (size) of a number by calling the Number.Abs function.

Sample code for the Number.Abs function

M Language Number.Sign Function

We can determine the sign a number by calling the Number.Sign function. It returns a value of -1 for negative numbers and 1 for positive numbers.

Sample code for the Number.Sign function

Most of the results seen below are what you expect from the functions.

Sample data from the functions

The [OddNumber] column seems to have a odd nature. No pun intended. The function does not work with negative numbers. The definition of a odd number by www.mathisfun.com is "Any integer (not a fraction) that cannot be divided exactly by 2."

I have submitted a bug request to the Microsoft Power BI team. However, how can we work around this bug right now?

The quickest fix to the problem is to re-write the formula as Number.IsOdd(Number.Abs([ZeroSum])). Another solution is to define a function in the M language that tests if the number is an integer and determines if 2 does not evenly divide the number.

Numerical Operations in the M Language

Now, we can concentrate on non-trigonometric, numerical operations supported by the M language. We will be adding computed columns to our Sample Data set to demonstrate each function. Each function can be used to solve a specific mathematical question.

M Language Number.IntegerDivide Function

How many times the [Divisor] divides into [Dividend] evenly? This question can be answered by calling the Number.IntegerDivide function.

Sample code for the Number.IntegerDivide function

M Language Number.Mod Function

What is the remainder of the [Dividend] divided by the [Divisor]? The answer to the question can be found by calling the Number.Mod function.

Sample code for the Number.Mod function

The results below are from a sample use of the Number.IntegerDivide and Number.Mod functions.

Updated example with the Number.IntegerDivide and Number.Mod functions

M Language Number.Power Function

The power (exponent) of a number says how many times to use the number (base) in a multiplication. The image below shows 2 raised to the fourth power.

The power (exponent) of a number

How do we raise a number to the two's power? This is also know as squaring the number. We can use the Number.Power function on the [Power] column to investigate this question.

Sample code for the Number.Power function

M Language Number.Sqrt Function

The opposite (inverse) of squaring a number is taking the square root of a number. I am only talking about positive squared results. Otherwise, we will have to talk about i imaginary numbers. In short, every positive square has two roots, a positive one and a negative one. Thus, we can replace 3 with -3 in the image below and have the same balanced equation.

Taking the square root of a number

The image below depicts the use of the Number.Sqrt function on the [Root] column. Almost all languages return just the positive square root of any number.

Sample code for the Number.Sqrt function

M Language Number.Factorial Function

The factorial function, expressed with the ! symbol, is defined as the multiplication of a series of descending natural numbers. Thus, three factorial written as 3! = 3 x 2 x 1 = 6. The image below illustrates the [Power] column expressed as a factorial using the Number.Factorial function.

Sample code for the Number.Factorial function

The results below are from a sample calls to the Number.Power, Number.Sqrt, and Number.Factorial functions.

Number.Power, Number.Sqrt, and Number.Factorial functions

M Language Number.Exp Function

Euler's number, expressed as a lower case e, is an important mathematical constant and is the base of the natural logarithm. It is approximately equal to 2.71828. The exponential function raises e to the power of x. This can be represented mathematically as (e ^ x).

The formula below defines the [Exp] column as the result of calling the Number.Exp function with the [Power] column.

Sample code for the Number.Exp function

M Language Number.Ln Function

The natural logarithm, expressed as ln, is the inverse function of exp. Therefore, ln (e^x) = x.

The formula below calls the Number.Ln function with the [Exp] column. Because we are applying the inverse, the result equals the original input, the [Power] column.

Sample code for the Number.Ln function

M Language Number.Log10 Function

The M language contains the common logarithm. It can be defined 10 raised to the x power equals the number y or 10 ^ x = y. The following equations are true: log10 (1000) = 3, log10 (100) = 2, and log10 (10) = 1.

The formula below defines the [Log10] column as the result of calling the Number.Log10 function with the [Power] column.

The M language contains the common logarithms

M Language Number.Log Function

The M language contains the binary logarithm. It can be defined 2 raised to the x power equals the number y or 2 ^ x = y. The following equations are true: log2 (8) = 3, log2 (4) = 2 and log2(2) = 1.

The formula below defines the [Log2] column as the result of calling the Number.Log function with the [Root] column.

Sample code for the Number.Log function

M Language Number.Combinations Function

In mathematics, a combination is a way of selecting k items from a total collection of n items. The order of the k items does not matter. However, the answer to this question can be expressed as the equation below. We can finally use that factorial function that was introduced earlier. Thus, how many ways we can select 2 items for a set of 4. This is mathematically equal to 4! / (4-2)! 2! = 6 combinations.

A combination is a way of selecting k items from a total collection of n items

The formula below defines the [Combinations] column as the result of calling the Number.Combinations function with the [Power] column as the set size and 2 as the selection size.

Sample code for the Number.Combinations function

M Language Number.Permutations Function

In mathematics, a permutation is a way of selecting k items from a total collection of n items. The order of the k items does matter! However, the answer to this question can be expressed as the following equation. Again, the factorial function is used in expressing the answer. Thus, how many distinct ways we can select 2 items for a set of 4. This is mathematically equal to 4! / (4-2)! = 12 permutations.

Permutation is a way of selecting k items from a total collection of n items

The formula below defines the [Permutations] column as the result of calling the Number.Permutations function with the [Power] column as the set size and 2 as the selection size.

Sample code for the Number.Permutations function

The results below are from a sample calls to the above functions.

Sample data from the functions

Numerical Constants in the M Language

The last topic to cover today is the numerical constants that are supplied by the M Language.

M Language Number.Epsilon Function

The Number.Epsilon function returns the smallest value in the M language.

Sample code from the Number.Epsilon function

M Language Number.E Function

The Number.E function returns Euler's number.

The Number.E function returns Euler's number

M Language Number.PI Function

The Number.PI function returns the ratio of a circles circumference to the diameter which is depicted by the Greek letter PI in mathematical literature.

The Number.PI function

M Language Number.PositiveInfinity and Number.NegativeInfinity Functions

The M language contains both a Number.PositiveInfinity and Number.NegativeInfinity functions. The definition of infinity describes something without limits. For instance, if we take a -1 and continuously divide by 2. This sequence of numbers is infinite in size, approaches zero and never reaches zero.

The image below shows the use of the negative infinity function.

The M language contains both a Number.PositiveInifinity and Number.NegativeInfinity functions

M Language Number.Nan and Number.IsNaN Functions

This next function is called Number.Nan which represents 0/0. Beginning programmers who have not coded defensively for division by zero might have been waken up in the night to fix such a bug. In mathematics, division by zero is undefined. The is a informational function Number.IsNaN that returns true if the column contains this value. In short, I just do not see any real use of these functions unless you are study some advanced mathematical structures.

Sample code for the Number.Nan function

The results below are from a sample calls to the above functions.

Sample data set with the functions

Summary

The Power BI desktop designer contains a rich set of advanced mathematical functions. We can combine these functions together in a computed column to solve numerical business problems. Some of these functions are not readably applicable to our day-to-day issues. Having knowledge of these functions is a good foundation to draw upon.

Before I wrap up this tip, I want to talk about one real use of the log10 function called the Logarithmic scale. This technique is used to graph the magnitude of some variable that has a large range of values.

One example that I can think of is the number of retail returns (R) over the course of weeks in a year. I can imagine that the numbers are relatively low for most weeks. However, I am sure there is a big spike during the holiday season. If we graphed this value R on the dashboard, we might have values ranging from 100's of items to thousands of items. On the other hand, if we graph log10 (R) we would have number between 0 an 5. The graph would be cleaner to show the spikes during special times of the year.

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 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools