fbpx
Email: [email protected] Call Now! +1 203-349-9909 / +91-8080042523

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

BEFORE DATA CONVERSION USING M LANGUAGE

Step 2:  Add Custom Column to Concatenate Text and Number Data

To add the Custom Column, go to-

Add Column  >  Custom Column

ADD 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 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.

DATA CONVERSION USING M LANGUAGE

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.

AFTER DATA CONVERSION USING M LANGUAGE

You can learn more :

Leave a Reply