If you’re new to Power Query, chances are you’re more comfortable doing tricky mathematics using Excel formulas, rather that Power Query formulas. No shame there, but you’ve probably run into a situation where you set up the formulas, refresh your query and the Excel formulas don’t update in Power Query 's output table.
I’ve worked with this issue for a long time, and it’s actually caused me to avoid using Excel formulas in tables generated via Power Query all together. Having said that, there is now an easy way to fix this which renders that avoidance obsolete.
The Issue: Excel Formulas don't update in Power Query tables
Let’s take a quick look at this scenario. We have a simple table called Animals as follows:
And it gets landed in another table. But in this table, we added a new column called “Est” to the end, which holds the following formula: =[@Price]*[@Quantity]
So far so good, but what happens when we add a new line to our Animals table and refresh it?
Plainly, this is not good at all!
The Fix: Excel Formulas don't update in Power Query tables
The fix is remarkably simple, once you know what to do:
Step 1: Change the Table Design Properties
- Select any cell in the OUTPUT table (the green one)
- Go to Table Tools –> Design –> Properties (External Table Data group)
- Check the box next to Preserve column soft/filter/layout and click OK
Now, at this point, nothing appears to change. In fact, even refreshing the table seems to make no difference.
Step 2: Ensure the Formulas are consistent
The reason the formulas didn’t fill correctly for us is different now. It is entirely based on the fact the formula in the last column is no longer consistent. Naturally, that means that Excel won’t auto-fill the formula, as it doesn’t know which is correct (the formulas or the blank cell.) We need to fix that before this will work for us.
- Copy from the first formula cell down the entire column (I've got reports that this DOES matter, and that copying from another cell may not fix it.)
Our data should now look something like this:
Step 3: Test it
And now, when we add new data and refresh the Power Query…
Wrap-up Thoughts
On my Excel 2016 this behavior is now default. I don’t know when it changed, to be honest. And if your behavior is different, I’d love to know. I’m running the Office Pro Plus subscription – first release.
On Excel 2010/2013, the old default of not updating the tables appears to prevail. It’s actually for this reason that I covered this, as it came up as a question in my Power Query forum.
I’m not sure if this is good or bad, but this setting can/must be managed for each output table individually. There doesn’t seem to be a way to set one behavior or other to apply to all tables. To be honest, I think they’ve got it right in Excel 2016, so at least it’s fixed if you’re current. (And for reference, my understanding is that this required a patch to Excel, not Power Query, which is why I suspect that we likely won’t see it fixed for Excel 2010/2013.)
