Number.ToText

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Number.ToText

What is Power Query?

Before we dive into the M code behind the Number.ToText function, let’s briefly discuss what Power Query is. Power Query is a data transformation and cleansing tool that is built into Microsoft Excel and Power BI. It allows you to easily connect to various data sources, clean and transform data, and load it into your workbook or Power BI report.

The Number.ToText Function

The Number.ToText function is a built-in function in Power Query that allows you to convert a number to text. Here’s an example of how it works:

Number.ToText(1234.56)

This will return the text "1234.56". You can also specify the number of decimal places you want to format the number to:

Number.ToText(1234.56, “#.00”)

This will return the text "1234.56". The "#" character in the format string represents a digit, while the "0" character represents a zero. So in this case, we're asking Power Query to format the number to two decimal places.

The M Code Behind Number.ToText

Now that we've covered the basics of the Number.ToText function, let's take a look at the M code behind it. When you use the Number.ToText function in Power Query, the M code that is generated looks like this:

let

Source = 1234.56,

ToText = Text.From(Source)

in

ToText

As you can see, the M code is actually quite simple. The "let" statement defines a variable called "Source" which contains the number you want to convert to text. The "ToText" variable then uses the Text.From function to convert the "Source" variable to text. Finally, the "in" statement returns the "ToText" variable.

Using Number.ToText with Formatting

While the basic Number.ToText function is useful, it's even more powerful when you add formatting to it. Here's an example of how you can use the Number.ToText function with formatting:

Number.ToText(1234.56, “#,##0.00;(#,##0.00)”)

This will return the text "1,234.56". The format string "#,##0.00;(#,##0.00)" specifies that we want to format the number with commas as thousands separators, two decimal places, and parentheses around negative numbers.

In this article, we've explored the M code behind the Number.ToText function in Power Query. We've seen how simple the code is and how you can use the function with formatting to get even more out of it. Whether you're new to Power Query or a seasoned pro, understanding the M code behind its functions can help you make the most of this powerful tool.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)