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.
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
Disable Background Refresh of All Power Queries to refresh queries before refreshing pivot tables.