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.
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
STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)
Excel 2016:
Excel 2013 & 2010:
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):
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.
Now you will see your changes take place.
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.
Congratulations! You have used a M formula for data conversions!
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)