Power Query Trap: Formula Autofill

If you output the results of Power Query to a table in Excel then it’s often really useful to be able to add an extra calculated column or two onto the end of this output table. I used to do this all the time when using Microsoft Query.

However, now I’ve switched to Power Query I came across this Power Query trap (and risk) that had me stumped for a little while but a little trial and error eventually provided a solution.

So here’s the scenario. We have a source table which we reference using Power Query and then output to another table in the same Excel file (I’m not doing any transformations in this example to keep it nice and simple)

Here’s the output table, and I’ve added an extra column called Formula (= Code x Cost )

If I add a new line to my Source Table, let’s say we add 6 and 60 to the blue Source Table, then, when I refresh my output table I would hope and expect my Formula to copy down automatically.

IT DOESN’T!!!

Here’s the refreshed output and the formula is missing!

This is not good, in fact it’s downright dangerous and briefly depressed me as how could such an amazing tool as Power Query not work as well as the old MS Query?

Undeterred I thought there must be a way to fix this….

And here it is….

Right Click in the output table and select Table > External Data Properties

Tick the box “Preserve column sort/filter/Layout

Click OK

I’d recommend you then copy your formula down the entire column again before trying the refresh (it’s not always necessary to do this but better safe than sorry).

Now when you refresh the formula and formatting will copy down nicely.