Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Converting and formatting numbers using the M Language


By:   |   Read Comments (1)   |   Related Tips: More > Power BI

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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.

notepad m language

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.

notepad import m language

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.

m sample code

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.

creating a new m query

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.

blank query screen

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.

query 1 number-data.m

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.

result from query

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.

Function

Description

Byte.From

Returns a 8-bit integer number value from the given value

Int8.From

Returns a signed 8-bit integer number value from the given value.

Int16.From

Returns a signed 16-bit integer number value from the given value.

Int32.From

Returns a signed 32-bit integer number value from the given value.

Int64.From

Returns a signed 64-bit integer number value from the given value.

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.

add custom column

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.

result set from query

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.

result set from query

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.

result set from query

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.

Function

Description

Single.From

Returns a Single number value from the given value.

Double.From

Returns a Double number value from the given value.

Decimal.From

Returns a Decimal number value from the given value.

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.

add custom column

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.

add custom column

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.

result set

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.

result set

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.

character-data.m

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.

Function

Description

Number.ToText

Returns a text value from a number value.

Number.FromText

Returns a number value from a text value.

Number.From

Returns a number value from any value.

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.

add custom column

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

result set

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author John Miner John Miner is a Data Architect at Blue Metal helping corporations solve their business needs with various data platform solutions.

View all my tips





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     



Wednesday, April 13, 2016 - 11:51:19 PM - cris ben Back To Top

so much fun using this.  I love http://convertmytext.com/number-to-text.php too

 


Learn more about SQL Server tools