Power Query: Missing in Transformation

Welcome to our Power Query blog. This week, I look at a method to make column transformations more dynamic.

John, one of my imaginary salespeople, has been busy submitting his expenses. I used this same data in Power Query: Dynamic Removals, but this time, I am using another method to dynamically transform my columns.

I extract my data to Power Query using ‘From Table’ in the ‘Get & Transform’ section of the Data tab.

I change the data type on Amount, Card, Cash, Mike and Cheque to ‘Currency’.

I do this by selecting my columns and setting the Datatype on the Transform tab (I could have used this option on the Home tab too).

I rename this step ‘Change to Currency’, as I am going to show what happens if John doesn’t have a column for Mike. I close and load this query and go back to my original worksheet:

I remove the Mike column and return to my query.

As expected, I get an error.  This is because the M code in the ‘Change to Currency’ step refers to Mike:

I am going to create a function that will only transform columns if they exist.  To do this, I create a new blank query.  I can do this from the Excel workbook by choosing ‘Blank Query’ in the ‘From Other Sources’ section of ‘New Query’ in the ‘Get & Transform’ grouping of the Data tab.

In the Advance Editor, I add the following M code:

This means that I only try and transform the column if it exists.  This will work for any column transformation I wish to apply. 

I call the function DynamicTransform.

I go back to the query which gave an error for the missing Mike column and adjust the M code in the ‘Change to Currency’ step:

Instead of using Table.TransformColumnTypes, I use my new function:

And now, the error does not occur:

I now have a dynamic way to apply changes to John’s columns even if some of them are missing.

Come back next time for more ways to use Power Query!