Power Query offers various functions for converting numbers to text in Microsoft Excel and Power BI: Text.From(), Number.ToText(), Text.PadStart(), Text.Combine()... find out all you need to know!
Within a company, it’s often necessary to convert numbers into text. This is a common operation in a wide variety of fields such as finance, data analysis, or Data Science.
Data is frequently presented in numerical form, and converting it into text makes it easier to present for improved understanding and interpretation. It also allows for integration into reports or presentations.
For instance, in finance, monetary amounts stored as numbers can be converted into text by including the relevant currency or units for integration into a report.
Similarly, in data analysis, converting numbers into text helps visualize and interpret the data better and simplifies the comparison of numbers with different orders of magnitude.
Moreover, converting numbers into text can facilitate the integration of data from different sources and formats, making it easier to align them for subsequent analysis.
In Microsoft Excel and Power BI, the Power BI data transformation tool offers various options for converting numbers into text. This enables professionals to save time and increase their efficiency. In this article, explore all the functions that make this task possible!
The quick and easy Text.From() function
The Text.From() function is extremely useful in situations where the user simply wants to convert a number into text as quickly as possible.
To use it, you only need to create a custom column and use the formula “= Text.From([Name of the column with the number])”.
For example, if you have a table with a “Price” column containing numbers, you can create a new “Price Text” column with the formula “=Text.From([Price])”.
Here’s a step-by-step guide:
1. Open Power Query and load the data into the table.
2. Select the “Price” column.
3. Click on the “Add Column” tab in the ribbon.
4. Select “Custom Column” from the dropdown menu.
5. Enter a name for the new column, such as “Price Text.”
6. Enter the following formula in the formula box: = Text.From([Price])
7. Click “OK” to add the column.
8. The new “Price Text” column will appear to the right of the original column. The numbers will now be converted into text.
What is the purpose of the Number.ToText() function?
In addition to converting a number to text, the Number.ToText() function allows you to specify the output format. Its two mandatory arguments are the number to be converted, and the format string, which can contain special characters such as commas, decimal points, currency and percentage symbols.
This is particularly useful for converting a number to a specific currency, or for adding decimals. This is particularly useful for formatting numbers before merging them with other strings, or displaying them in reports or dashboards.
To use the Number.ToText() function, you can create a custom column and add the formula “= Number.ToText([Name of the column with the number], “Output format”)”.
Let’s take the example of a table with a “Amount” column containing numbers. To convert it into dollars with two decimal places, you would create a new “Amount in dollars” column with the formula “= Number.ToText([Amount], “C2″)”.
Here are the detailed steps:
1. Open Power Query and load the data into the table.
2. Select the “Amount” column.
3. Click on the “Add Column” tab in the ribbon.
4. Select “Custom Column” from the dropdown menu.
5. Enter a name for the new column, such as “Amount in dollars”.
6. Enter the following formula in the formula box: = Number.ToText([Amount], “C2”)
7. Click “OK” to add the column.
8. The new “Amount in dollars” column will appear to the right of the original column. The numbers will now be converted into text with the specified format.
Why use the Text.PadStart() function?
The Text.PadStart() function allows you to fill a character string with padding characters on the left until it reaches a specified length as defined by the user.
This function is particularly useful in situations where you need to add leading zeros to reach a certain length, helping to align data for easier comparison.
To use it, you need to create a custom column with the formula “= Text.PadStart(Text.From([Name of the column with the number]), Length, “Padding character”)”.
For example, consider a table with an “Order ID” column containing numbers. To add leading zeros and achieve a length of 6 characters, you can create a “Formatted Order ID” column with the formula “= Text.PadStart(Text.From([Order ID]), 6, “0”)”.
Here are the step-by-step instructions:
1. Open Power Query and load the data into the table.
2. Select the “Order ID” column.
3. Click on the “Add Column” tab in the ribbon.
4. Select “Custom Column” from the dropdown menu.
5. Enter a name for the new column, such as “Formatted Order ID.”
6. Enter the following formula in the formula box: = Text.PadStart(Text.From([Order ID]), 6, “0”)
7. Click “OK” to add the column.
8. The new “Formatted Order ID” column will appear to the right of the original column. The numbers will now be converted into text with a fixed length of 6 characters, adding leading zeros if necessary.
How do I use the Text.Combine() function?
The fourth method for converting numbers into text is the Text.Combine() function. This function allows you to concatenate multiple character strings into one.
What is it used for? Well, it can be particularly useful for combining numbers with text.
For example, if a table has a “Year” column with numbers, you can add the text “Fiscal Year” in front of each number using the formula “= Text.Combine({“Fiscal Year “, Text.From([Year])}, “”)”.
This function enables you to merge text and numbers seamlessly, creating a combined string that can be used for various purposes in your data analysis or reporting.
Conclusion: Power Query, a powerful tool for converting numbers into text
Power Query makes it incredibly easy to convert numbers into text using simple formulas while offering specific formatting options.
The various functions within Power Query allow you to tailor your approach to the specific needs of each use case, providing versatility and flexibility to Excel and Power BI users.
Beyond text-to-number conversion functions, Power Query offers numerous data transformation possibilities. To learn how to master it, you can choose DataScientest’s Power BI training program.
This program enables you to gain complete mastery of Power BI in just 5 days and pass the Microsoft PL-300 exam to obtain the Power BI Data Analyst Associate certification.
All our training programs are conducted online via the web, and our organization is eligible for financing options – Discover DataScientest!
