Querytable FillAdjacentFormulas

[CharlieHall]

I have set up a powerquery that feeds a table in my workbook.

I would like to refresh this table via vba

the macro recording code showed the use of listobject.querytable

I tried this and in general it works. However, it does not fill the adjacent formulas.

When I set this parameter to true, then I get an error during execution of the macro. When it is set to false, then the formulas are not filled?

I am using Excel 2010.

Any suggestions?

Thanks

 

[Ken Puls]

Yeah, that's a bit of a pain, but the good news is that it's easy to fix.

• Go to the table in the workbook that comes from your Power Query and select any cell in it
• Go to Table Tools --> Design --> External Table Data (group) --> Properties
• Check Preserve column soft/filter/layout
• Click OK

Next, make sure your formulas are filled all the way down the table. From this point on, it should fill those formulas when you do a refresh that adds new rows (whether you refresh manually or via VBA.)

The only downside here is that this is not the default setup, so each time you are going to use formulas in a Power Query output, you need to change this setting.

 

[CharlieHall]

Thanks - that was the problem - my formulas were not filling automatically in the table

 

[Ken Puls]

No worries. I figured this hits enough people that I wrote up a blog post on the subject.