Is Power Query Refresh Broken?

Mr. George S 0 Reputation points
2024-06-12T08:26:04.3733333+00:00

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.

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,681 questions
Microsoft Q&A
Microsoft Q&A
Use this tag to share suggestions, feature requests, and bugs with the Microsoft Q&A team. The Microsoft Q&A team will evaluate your feedback on a regular basis and provide updates along the way.
685 questions
{count} votes

1 answer

Sort by: Most helpful
  1. S.Sengupta 17,136 Reputation points MVP
    2024-06-15T01:42:32.9033333+00:00

    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
    
    
    0 comments No comments