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

Inventory Your Files Using Power Query Power Query lets you perform a series of steps to transform your Excel data. One of the possible steps that surprised me was that I could list out the files in my computer!  This is useful if you want to do some analysis on your file list (i.e. finding a file, checking whic...
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...
Import Data from XML Using Power Query or Get ... Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data. But what if your data source is not in your Excel spreadsheet? It's very common nowadays to get data imported from a company's accounting or sales system in t...
Import Web Data Using Power Query   Power Query lets you clean and transform your raw & messed up data into a format where you can do further Excel analysis with ease. Firstly, we would need data to play with Power Query, right?  The good thing with Power Query is that there is a multitude of ways ...