NEW! FREE 30 Minutes MS Excel - Working with Dates Course  Watch now 

Power BITutorials

Data Type Conversion With ‘M’ Using Power Query

1 Mins read
DATA CONVERSION USING M LANGUAGE

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

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 :

Related posts
Power BITutorials

Opportunity Analysis Power BI Dashboard

1 Mins read
Information: A software company that has two sales channels: direct and partner. The sales manager created this dashboard to track opportunities and…
Power BITutorials

Procurement Analysis Power BI Dashboard

1 Mins read
Information: It analyzes a manufacturing company’s spending on vendors by category and location. we explore these areas: Who the top vendors are…
Power BITutorials

Retail Analysis Power BI Dashboard

1 Mins read
Information: It analyzes retail sales data of items sold across multiple stores and districts The metrics compare this year’s performance to last…