Is Power Query Refresh Broken?

Question

I'm experiencing an issue with Power Query refresh in Excel. I'm using Excel Microsoft Office Home and Business 2016 and the issue started after the May 30th, 2024 update. Here's a detailed description of the problem:

• I upload data from various sources using Power Query.
• When I refresh the data, everything refreshes at the Power Query level (I see the new information in Power Query Editor).
• There are no error messages displayed during the refresh process.
• However, the new information isn't reflected in the final table in the Excel worksheet (new data is not loading to the table).
• I need to manually manipulate table properties and refresh again for the new data to appear. To get the new data to appear, I typically need to manually modify the external data properties in the table.
• Specifically, I uncheck the boxes for "Insert cells for new data, clear unused cells," and then select "Insert entire rows for new data, clear unused cells".

After making these changes and refreshing again, the new data appears in the table. I've noticed that manually modifying the external data properties in the table after refresh seems to work as a workaround. However, is there a way to configure Power Query to automatically update the table without requiring this manual step? This issue is affecting all my colleagues, and the resulting data suffers from this delay.

Answer 1

Instead of manually adjusting table properties each time, you can use a macro to automate this process. Here's an example VBA script that can help automate the refresh and adjust the table properties:

Sub RefreshAllQueries()
    Dim conn As WorkbookConnection
    Dim lo As ListObject
    
    ' Refresh all queries
    For Each conn In ThisWorkbook.Connections
        conn.Refresh
    Next conn
    
    ' Update table properties
    For Each lo In ThisWorkbook.Sheets(1).ListObjects
        With lo.QueryTable
            .AdjustColumnWidth = False
            .PreserveColumnInfo = True
            .RefreshStyle = xlInsertEntireRows
        End With
    Next lo
    
    ' Refresh the workbook
    ThisWorkbook.RefreshAll
End Sub