Power Query: Uniting Different Types

Welcome to our Power Query blog. Today I look at how to join columns of data when the data type does not match.

I have some expense information in my Excel worksheet, and I’d like to combine the two columns expense code, which is data type ‘General’ and Expense Category, which is data type ‘Number’:

I am going to use Power Query to do this, so I select all my data and go to the ‘POWER QUERY’ tab. In the ‘Excel’ section I choose ‘From Table/Range’.

The ‘From Table’ sub-screen pops up to confirm the data which I want to load to my query, and I click ‘OK’.

I want to create a ‘Custom Column’ (from the ‘Add Column’ tab) which will combine my expense code and Expense Category.  The icons to the left of the column names show that these columns have different data types.  I try simply appending the columns with an ‘&’ viz.

There do not appear to be any syntax errors so I go ahead and try this.

Clearly, this hasn’t worked, and if I click on the ‘Error’ (highlighted) I get more detail:

Therefore, in my custom column, I need to add some specific M code that will allow me to combine my expense columns.  I will convert my numerical column to text.  I delete the ‘Added Custom’ step and try again…

This time, the M code I have used is

Note that M code is case specific, so the capital and lowercase letters should appear exactly as in the example above.  I have used the formula ‘Text.From’ to read the text from my numerical column.  I have also added a space so that my data will be legible and click ‘OK’ to see the results:

My text and numerical columns have been combined to create a new text column.  There are other useful formulae in M code which may be accessed from the ‘Custom Column’ screen by clicking on ‘Learn more about Power Query Formulas’ and exploring.  Just some of the many ‘Text’ formulas are show below:

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!