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

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Split First & Last Name Using Power Query There are times when you receive a data set of employee full names in one column and you want to separate the full name into first name and surname in separate columns. This is a common task and most people may refer to complex formulas to do this and waste lots of time in the...
Remove Rows With Errors Using Power Query Power Query lets you perform a series of steps to transform your messy Excel data. One of the most common steps I do, is  to clean my data and remove rows that have erroneous data, like this: Thankfully Power Query has an option that allows us to remove rows with errors...
Split Column By Number of Characters Using Power Q... 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 split column by number of characters easily. This is helpful when you have columns that ...
Group Rows and Get Counts 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 group rows and get the counts of each group very easily. DOWNLOAD EXCEL WORKBOOK Let’s go through the steps in detail:   STEP 1: Select yo...