Power Query lets you perform a series of steps to clean & transform your Excel data.

There are times when we want to do things that are not built in the user interface.  This is possible with Power Query’s programming language, which is M.

One of the unique characteristics of M is it takes data types very seriously.

For example, if we try to concatenate or combine a text with a number, it will result in an error.  So let us take a look what are our options for data type conversions in M.

DOWNLOAD EXCEL WORKBOOK

 

Let’s go through the steps in detail:

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

data-type-conversions-with-m-01

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 merge-columns-02

Excel 2013 & 2010:

from table

 

STEP 3: This will open up the Power Query Editor.

Go to Add Column > Add Custom Column

We want to combine the Financial Year (Number) and Sales Qtr (Text):

data-type-conversions-with-m-02

 

STEP 4: Let us create a simple M expression to combine the Financial Year and the Sales Quarter.

In the New column name text box, type Financial Quarter

In the Custom column formula, type in: Text.From([FINANCIAL YEAR])&” “&[SALES QTR]

The main point here is we are doing the following steps:

  • We are using the Text.From formula to convert the Financial Year into Text
  • Since Financial Year and Sales Qtr are now both texts, we can combine them together.
  • We can now use the Ampersand (&) to join them together.

Click OK.

data-type-conversions-with-m-03

Now you will see your changes take place.

data-type-conversions-with-m-04

 

STEP 5: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated values.

data-type-conversions-with-m-05

Congratulations! You have used a M formula for data conversions!

data-type-conversions-with-m-06

There are a lot more data conversion formulas you could use:

  • Date.FromText() – Converts text dates into the date data type
    • ex. Date.FromText(“Jan 12, 2017”)
  • Time.FromText() – Converts text times into the time data type:
    • ex. Time.FromText(“6:55 AM”)
  • Number.FromText() – Converts text numbers into the decimal data type:
    • ex. Number.FromText(“123.34”)
  • Currency.From() – Converts values into the currency data type:
    • ex. Currency.From(500.15)

 

data-type-conversions-with-m

HELPFUL RESOURCE:

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Replicating Excel’s LEN Function with M in P... Power Query lets you perform a series of steps to transform your Excel data. There are times when we want to do things that are not built in the user interface. This is possible with Power Query's programming language, which is M.Unfortunately not all of Excel's formulas can ...
Remove Columns Using Power Query or Get & Tra... Power Query (in Excel 2010 & 2013) or Get & Transform (in Excel 2016) lets you perform a series of steps to transform your Excel data.One of the steps it allows you to do is to remove columns easily.This is helpful when you have columns that you want to eliminate ...
Reverse Rows Using Power Query Power Query lets you perform a series of steps to transform your Excel data.  One of the steps it allows you to take is to reverse the order of rows very easily.DOWNLOAD EXCEL WORKBOOK To provide a quick comparison, it's not straightforward to reverse rows in Exc...
How To Install Power Query in Excel 2010  Power Query is a new add-in that was introduced in Excel 2013 by Microsoft and was that popular that they made it backward compatible with Excel 2010.CLICK TO SEE THE TUTORIAL ON HOW TO INSTALL POWER QUERY IN EXCEL 2013  So what is Power Query? We...