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.)
11 thoughts on “Fix: Excel Formulas don’t update in Power Query tables”
Nice. Thanks for sharing. do you know if it solves the "comments" issue where comments in a new column against cat get moved to gerbil? I assum so based on the property name
Hey Matt, not using that option, no. In fact it gets really ugly! But under the checkbox there are three radio button options. If you choose "Insert entire rows for new data, clear unused cells" it seems to keep the comments lined up nicely.
I have noted that this approach, since it preserves the column sort, causes new columns to always be placed at the right side of the table. This can be ok, but it depends on what you are doing on the sheet.
@response to Matt: I usually use "Overwrite existing cells with new data, clear unused cells" - but are either of the other two selections better? Is there a definitive explanation of these options - because I have not found one yet.
@Alex, I'm not sure what the difference is, to be honest. I thought it would be in the deletions, but further testing shows that is not the case.
@Matt, I'd be careful with what I said in the comment above. Further testing shows that this works great for insertions, but when items are removed from the data source, we still seem to end up with shifting rows no matter what option we choose. I'm not sure if this is a bug or by design. (If the latter, it would seem a poor one!)
Amazing! Thank you! I had spent the better part of a day trying to develop an VBA solution. Should have come here first!
The only way I got this to work constantly with varying table sizes ( depending on the outcome of a power query ) is to use the auto-fill button to do the copying of the formulas. double click on it right through any gaps in the formula, all the way down to the very last current row.
Strange. My steps should have worked for you regardless of the shifting output size for a table. The problem with the auto-fill button to copy the formula is that you still have to copy the formula, so you're right back to manual -- exactly what I want to avoid.
Excellent tip, I have been struggling with this issue for some time, which is now solved, thanks to Google and your article!
Thanks a lot! This has worked for me 😉 Interestingly on my Excel 2016 this wasn't default behavior
This helps a lot. Thank you so much.
THANK YOU SO MUCH!!!! This was driving me crazy that I couldn't get the formulas to auto-fill. I was seriously considering scrapping a lot of work I'd already put into this approach and you fixed it in an instant.