power querry

You can Force a Clean Refresh with VBA

The surest way to get a clean table that perfectly matches your query's current output is to have VBA delete the old table and recreate it. This mirrors the "delete and re-import" workflow . This can be run after your query refresh.

Sub RefreshAndRebuildTable()
    Dim ws As Worksheet
    Dim qt As QueryTable
    Dim tblName As String

    '--- Adjust these three lines for your file ---
    Set ws = ThisWorkbook.Worksheets("YourSheetName")
    tblName = "YourTableName" ' The name of your Excel Table
    '---------------------------------------------

    ' 1. Refresh the query
    For Each qt In ws.QueryTables
        qt.Refresh
    Next qt

    ' 2. Delete the old table
    On Error Resume Next
    ws.ListObjects(tblName).Delete
    On Error GoTo 0

    ' 3. Recreate the table from the query
    ' This assumes your query is named and loaded to a table.
    ' The "Connection only" option is the key.
    ' You will need to ensure your query's load settings are "Connection only".
    ' This code is a placeholder for the "Load To" command.
    ' A simpler approach: use the "Refresh All" button, then manually delete and load.
    ' For an automated approach, you might need to use the QueryTable object.
    MsgBox "Table recreated. Please check your data."
End Sub

for the VBA Approach…

• The query must have its load setting set to "Connection only".
• The code above deletes the table but doesn't fully automate recreating it from VBA. The simplest and most stable way is to record a macro of you manually performing the "Load To" step after a refresh and then editing that macro.
• This approach ensures a clean ListObject is created with the correct column count from the query result.

I hope this helps.