This article provides VBA macro code to copy or fill down the formulas in a Table after it is refreshed.
Are you getting blank cells in columns that contain formulas after you refresh a Power Query Table? When new rows are added to the table, sometimes the formulas are not copied down. You will get blank cells like the following image.
I don't know of any solution to this in Excel, but you can use a simple line of VBA code (macro) to fill the formulas down.
The following line of code will copy the formula in row 1 of the Table, and fill it down (paste) to all the rows in the Table.
Range("Table1[[Revenue]:[Markup]]").FillDown
You will just need to change the Table name (Table1) and the column names ([[Revenue]:[Markup]]) to match the names in your Table.
The following macro will refresh all the Power Query connections (Tables) in the workbook, then fill down the formulas in Table1.
Sub Refresh_Power_Queries_FillDown_Table_Formulas()
'Description: Refresh all queries and fill formulas down in Excel Table
'Author: Jon Acampora, Excel Campus
'Source: https://www.excelcampus.com/vba/copy-down-table-formulas-power-query-refresh/
'Refreshes all Power Query connections
ActiveWorkbook.RefreshAll
'Fill down formulas in the PQ output table
'Change the table name and column names of the
'columns that contain formulas
Range("Table1[[Revenue]:[Markup]]").FillDown
End Sub
Related Articles
Disable Background Refresh of All Power Queries to refresh queries before refreshing pivot tables.
Hi Jon,
Thanks for explaining how you have corrected the formula filldown issue with VBA. I anticipate having to use this solution although it opens us up to things going wrong when macros are not enabled etc… Have you heard anything more about how to fix this without code or if this is a bug that will be corrected down the line?
Hi Christopher,
Great question! In my opinion, and the opinion of a lot of other Microsoft MVPs, this is a bug that should be fixed. However, I’m not sure if it will ever be fixed.
One workaround is to create a button that runs a macro to refresh the queries and copy down the formulas. Then instruct the users to use this button to do the refresh, instead of using the Refresh button on the data menu or right-click refresh on the table.
This will ensure that all the steps are taken to fully refresh the query and copy down the formulas. I hope that helps. Thanks!
Hi John,
There is a solution to this.
Select cell in table –> table tools –> Properties –> Check box “Preserve column soft/filter/layout and click OK”
However! this needs to be done before the Excel table column is added (and calculated) to the table from the Query.
Thank you Mads! I’ve found that is also depends on how the formula is copied down. The fill handle seems to perform better than copy/paste for some reason.
Hello,
I’ve been having an issue with Microsoft Excel 2016 and can’t seem to be able to sort it out. I have a query which returns data that varies in size, depending on the day. Here is my issue.
Day 1: Data is returned with 10 rows total (including header row). 3 columns to the right have formulas (from 2nd row down; 1st row is header). VBA macro updates both query and fills down adjacent formulas correctly.
Day 2: Data is returned with 2 rows total (including header row). Instead of adjacent formulas being populated, the header has now been copied down.
This issue only happens when either I have 1 data row or blank data row returned. I never used to have this problem and have been using the same file for 2.5 years. It is just recently this started to happen (probably in line with Microsoft Office upgrade)
Help is appreciated!
Hi Melissa,
That is an interesting issue. I’m not able to recreate it, but I believe you are right that it could be due to an update. You might need to add some code to check if the table only contains 1 row or a blank row, then do not do the fill down if that is the case. I hope that helps.
this code “Range(“Table1[[Revenue]:[Markup]]”).FillDown ”
looks exactly what i need, but for some reason im getting all the time the following error: Runtime error: ‘1004’ Application defined or object defined error. when the table refreshes.
i’m sure i have entered the correct tablename and column names and used the exact format above.
I’m using excel 2013.
My scenario is that i have a table filled through an sql excel connection, column A:D and column E:F contain formulas which need to be filled down.
The table showsthe correct size (column A:F)
any clue why this error is happening?
I have my report set to refresh a Query, then filldown, then refresh the pivot table that references the query table.
For some reason, I have to run the Macro Twice (Which I have in the form of a button) and I really don’t want that.
Sub Runthrough()
‘
‘ Runthrough Macro
‘
‘
ActiveWorkbook.Connections(“Query – Purchasereceipt”).Refresh
Range(“Purchasereceipt_2[[Grade]]”).FillDown
Sheets(“Pivot Table”).PivotTables(“PivotTable1”).PivotCache.Refresh
End Sub
Thoughts?
Hi Jon,
Thanks for this, this seems to be exactly what I needed, but I’m not certain. If this isn’t what I need, can you offer another suggestion.
I have 3 worksheets in my workbook with data. Sheet 1 contains data from 3 year data range, Sheet 2 contains data from a 2 year data range, sheet 3 is the result of using the append function to join sheet 1 & 2 together. (there is much data for it to be managed with just 1 data pull). Sheet 1 & 2 have the necessary formulas in 19 of 39 columns.
When I do my refresh, I either get blanks or just the values in the columns with formulas.
I assume the macro you have provided will work for my needs, but do I need a separate line of code for each column, or can I list them all in the 1 line? They are not necessarily beside each other in the worksheet. Also, some column headers are 2 (or more) words long, do I need to indicate the space with “_” like a table name, a regular space or no space?
Thanks so much.
Hi Jon
I have used a combination of two of your blog posts, the first being to Refresh All connections whenever there is a change to the source data and the second, to Fill Down Formulas after Power Query has run.
The refresh all when there is a change, works like a charm. Thank you so much, this will make my models so much more robust. The problem happens when i try and combine the formulas to fill down after Power Query has run, i get the below error.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.RefreshAll
Range(“NameTable[[Total]”).FillDown
End Sub
Run time Error ‘1004’:
Method ‘Range of object’_Worksheet’failed
Please could you advise where i am going wrong as the combination of these two macros operating perfectly together will really take my financial models to the next level and save me significant time.
Your assistance would be greatly appreciated
Hi Jon,
I created this fill down but get this error when run:
Run-time error ‘1004’
Method ‘Range’ of object’_Global’ failed
Any idea where I’ve gone wrong?
Thanks !
hi…i want excel formul tables
how to autoformat the power query to 1,000? Many thanks