Data Type Conversion With ‘M’ Using Power Query

Data Type Conversion With ‘M’ with Power Query. 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 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