Converting and formatting numbers using the M Language

By: John Miner   |   Comments (1)   |   Related: > Power BI

Problem

Power BI designer is based upon the Power Query formula language informally known as "M" and in this tip we look at how to convert and format numbers using the M language.

Solution

Unfortunately, the advanced editor inside the Power BI designer does not support syntax highlighting. It can be really easy to make mistakes. How can we extend the Notepad++ editor to support this feature?

Matt Mason has supplied the community with a language file for Notepad++. Just download the language file from his blog entry.

Open the Notepad++ editor. Choose the language menu at the top. Next, select the define your language sub-menu. You should have the above dialogue box at this point. Click the import button to load the "mlang_npp.xml" file.

Converting and Formatting Numbers with M language

Today, we are going to learn about how to convert numbers saved in text data types to a numerical data types. Knowing the positive and negative limits is an important consideration. Trying to stuff a large number into a small numerical data type will result in a error.

The image below shows the first M language file that we are going to work with today. You can download the sample files here.

One powerful concept in this language is a list. The MyData statement creates a list of numbers from -64 to 64. The Table.FromList function converts our list of numbers to a table object that we are familiar with. By default, the first field is named column1. The next statement uses the Table.RenameColumns function to change the name to [Row].

The next two statements use the Table.AddColumn function to create computed columns. I wanted a [No] field to reflect the current row number. The [Limits] column has a complex formula. We wanted to raise 2 to the power of absolute value of the [Raw] column. If the [Raw] column is negative, make the result negative and add one. If the [Raw] column is positive, subtract one from the final result.

Right now, the data would be returned as a numerical data type. This will not do, since I want to pass the first set of M language functions a text data type. The Number.ToText function is applied to convert the final result to a text data type.

Blank Query

We want to write a M language statement without using the menus. Again, choose the get data button from the ribbon. Find the blank query type on the other sub-menu.

Choosing the connect button should open the query designer with a blank query. Choose the advanced editor button off the ribbon. This will open a dialogue box with an M language starter statement.

Go to the "numbers-data.m" file that is loaded in Notepad++. Select all the text and copy the information to the clip board. Go to the advanced editor dialogue box and replace the text with the program I supplied. Your window should look like the image below.

Clicking the done button will parse the program. It if is syntactically correct, it will execute. The resulting data will show in the query designer.

Now that we have sample data in the model, I can start talking about conversion functions.

Exact Numerical Conversions

The M language has many different types of numerical conversion functions. Right now, we are going to talk about exact numerical value functions. These are functions that take any data type as input and return a natural number, also known as an integer. The table below shows each function.

If you have been reading my tips, you should now be an expert at adding custom columns. The above functions take three parameters. Parameter one is the value in any data type. Parameter two is the culture. For instance, some cultures use a comma instead of a period for decimal points. Parameter three is the rounding function. It uses the RoundingMode.ToEven as a default. See my rounding tip if you have any questions.

Going forward, I will pick one example for a group of similar functions.

A sample use of the Byte.From function wrapped in error handling. Because the values in the [Limits1] column can exceed the supported values of some data types, we will turn those errors into null values.

One piece of information that is lacking from MSDN pages is the range of values supported by each data type. This example is actually supplying us with that information by using trial and error. The Byte.From can convert values from 0 to 255 to a byte. The Int8.From can convert values from -127 to 127 to a Int8. The Int16.From can convert values from -32,767 to 32,767 to a Int16.

More bits in the data type equates to a larger range of values that can be stored. The Int32.From can convert values from -2,147,483,647 to 2,147,483,647 to a Int32.

Last but not least, we will look at a big integer data type. The Int64.From can convert values from -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) to a Int64.

What I do not understand is why scientific notation is being used. We can see that 2 raised to the 56 power is still represented as an integer. This might be another bug to open with Microsoft.

To recap this session, exact numerical conversions deal with integers. Next, we will be talking about real numbers that can have digits right of the decimal point.

Approximate Numerical Conversions

The M language has many different types of numerical conversion functions. Right now, we are going to talk about approximate numerical value functions. These are functions that take any data type as input and return a real number, also known as a floating point number. The table below shows each function.

The [Limits1] column was used to represent powers of two. All of the values were integer in form. We will use the exponential function I introduced last time to use raise Euler's number to the power of [Raw]. The results from this calculation will be a floating point number.

The [Limits3] column is defined as such. The [Limits2] column is divided by 4 to result in smaller floating point numbers. The results of both are converted to text. See the image below for the exact formula.

A sample use of the Single.From function using the [Limits2] column. I did not wrap the function in error handling since all values are within range.

The image below shows the sample use of all three approximate numerical conversion functions. It is interesting to note that both the Double.From and Decimal.From functions return different results given the same input. It seems that the decimal function has less precision than the double.

In summary, these conversion functions take two parameters. Parameter one is the value in any data type. Parameter two is the culture.

The Currency.From function does not fit in either category. It is not a integer and it is not a true decimal. It has fixed precision of 4 digits. I always wondered how a cashier at a store could give me back 2.4999. Obviously, the digits are used by the banking industry with APR calculations.

Other conversions

There are only three more functions that we have not formally covered. Two functions return a generic data type called numeric. One function accepts a generic data type called any. To work with these functions, we will need to load some character data.

The image below shows the second M language file that we are going to work with today.

This program generates a table with two columns. The [Raw] column contains numbers from 1 to 255. The [Alpha] column contains the ASCII character represented by this number.

The table below shows the three remaining functions. We have used the Number.ToText in our formulas above. So, I will not cover it here. However, I do suggest you look at the MSDN entry on this function. If you are familiar with the C# String.Format function, you will be right at home.

Both the Number.FromText and Number.From functions have a similar call patterns. Parameter one is the value to convert. The first function only accepts a text value as input. The second function accepts any value as input. Parameter two is the culture (language) identifier. The return value of both functions is a generic number data type. One needs to wrap these function calls with an error handler.

The image below shows a custom column [To-Num1] with a sample formula. The [To-Num2] column demonstrates a call to the remaining function.

The results below identify ASCII characters 48 to 57 as being valid numbers. All other characters are converted to null values.

Summary

Today we concentrated on converting any data type to a named numeric type. The key point to remember is that each integer data type has a fixed range of values that it can represent. Exceeding this range at either the negative or positive end will result in a error. Floating point numbers use exponential notation with a fixed amount of precision. Last but not least, the currency data type is available for all those financial people. Wrapping these functions with error handling may be prudent.

When converting numbers to text, the business intelligence developer can supply a format string to pretty print the data. With most of these functions, an optional culture string can be supplied since different countries represent numeric data in different ways.

Next Steps

• You can download the sample files here.
• Check out these other M language tips
  • Introduction to the Power BI Desktop and the M Language
  • Rounding Numbers using the M Language
  • Simple mathematical formulas using the M Language

Learn more about Power BI in this 3 hour training course.

About the author

John Miner is a Data Architect at Insight Digital Innovation helping corporations solve their business needs with various data platform solutions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips