Data Type Conversions with M in Power Query

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.

Key Takeaways

• Essential for Accurate Analysis – Converting data types ensures correct calculations and logical operations in Power Query.

• M Language Handles Conversions – Power Query uses M functions like Number.From, Text.From, and Date.From to change data types.

• Automatic vs. Manual Conversion – Power Query often auto-detects data types, but manual conversion gives you greater control.

• Use the ‘Changed Type’ Step Wisely – The automatically inserted step can be modified or removed to avoid unexpected results.

• Combine Type Conversions with Error Handling – Pair conversions with try...otherwise statements to avoid breaking queries on bad data.

Data Type Conversions with M in Power Query

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)

 

Frequently Asked Questions