Forum Discussion
power querry
Hi ,
Thanks a lot for your answer ! I'm surprised that Power Query's default behavior isn't to clear the previously created table.
After reflection, I think the simplest and most robust approach for my use case is to totally replace Power Query with a VBA macro. The macro will:
- Loop through a user-defined folder containing multiple workbooks
- For each workbook, read data from specific sheets
- Paste the values into a destination workbook, after clearing the target range first
Because the macro clears the destination before each copy-paste, there is no Table object holding onto old column structures between runs — the ghost column problem simply cannot occur.
Of course, it's a copy paste method but, at the end, I am pretty sure it's gonna do the work.
You're absolutely right about the ghost column issue. Here's the clean VBA hybrid solution that permanently solves it while keeping Power Query's power:
Vba code
Sub PQ_RefreshAndRebuild()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("YourSheetName")
' Delete table before refresh to remove ghost columns
On Error Resume Next
ws.ListObjects("YourTableName").Delete
On Error GoTo 0
' Refresh recreates table with correct column count
ThisWorkbook.RefreshAll
End SubRequirements…
- Query load setting: Must be "Table" (not "Connection only")
- Table name: Must be fixed (update "YourTableName" in the code)
- Sheet name: Must match (update "YourSheetName" in the code)
Your query's load setting must be set to "Table" (not "Connection only"). But since you're already using Power Query tables, that's already the case. So no issue🙂.
Thanks a lot. Yes I already used "Table" :) I am really surprised to encounter this issue with PQ. I used PQ to avoid VBA, and in the end I still need to use VBA to make my PQ work properly.
