Sometimes it happens that the things we want to do are not built in the user interface. But Power Query’s programming language M has made this possible.
Data Type Conversion With ‘M’ Using Power Query
One of the unique functionality of M is that it takes data types very seriously.
If we try to concatenate a text with a number, it will result in an error. Using Power Query M you can overcome with this issue.
Step 1: Select the columns that you want to concatenate
When you load the data into Power Query, select the text column and the number column for applying a formula. In this case, we have selected the ‘Sales Month’ and ‘Qty Sold’ columns as shown in the below picture.
Step 2: Add Custom Column to Concatenate Text and Number Data
To add the Custom Column, go to-
Add Column > Custom Column
Step 3: Concatenate Text and Number Data using M
Concatenating text and number data together is a type of data conversion that M language helps us to carry out.
Let’s create a simple M expression to combine the ‘Sales Month’ and ‘Qty Sold’ columns.
In the New column name text box, write Monthly Sales.
The custom column formula includes the following:
Text.From([Qty Sold])&” “&[Sales Month]
Where,
Text. From function converts, the Qty Sold into Text
Since Sales Month and Qty Sold are now both texts, we can combine them together. Also, use the Ampersand (&) to join them together and click OK.
Step 4: Close and Apply
After you click on OK, you will get to see a new column added showing the concatenated text and number column as shown below. Click on Close and Apply button to save the changes.
You can learn more :
- Learn How to Use Advanced Editor in Power Query
- Delete Steps Until End in Excel
- Use Excel’s Right Function With M To Extract Left Side Data
- Use Excel’s Right Function With M To Extract Right Side Data