power querry

Question

Hi everyone,I've run into a recurring issue: after refreshing a Power Query that's loaded into an Excel Table, a completely blank column sometimes appears that doesn't exist in the query output itself. In my case the table starts at cell B2, and the blank column shows up right next to my actual data.My working hypothesis is that this happens when the number of columns returned by the query decreases compared to a previous refresh: Excel's Table object doesn't always shrink to match the new result, so it keeps a "ghost" column inherited from the previous load's structure. However, This behavior seems unpredictable: when I went from 10 columns down to 5, I could only got one extra blank column, not five.Has anyone else experienced this? Is there a reliable way to force the Table to always match the query's current column count exactly, instead of refreshing on top of the old structure? And if the "ghost column" theory is right, why would only one extra column show up regardless of how many were actually dropped?Thanks in advance!

nikolinode · Answer

You can Force a Clean Refresh with VBAThe 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&nbsp;. 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 Subfor the VBA Approach…The query must have its load setting set to&nbsp;"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&nbsp;ListObject&nbsp;is created with the correct column count from the query result.&nbsp;I hope this helps.

léa · Answer

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&nbsp;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.&nbsp;&nbsp;

riny_van_eekelen · Answer

Léa​&nbsp;I don't recognize your problem and the last thing I would do is choose VBA over PQ. Would you mind sharing the link to a file (on OneDrive or similar) that demonstrates the issue?Or else, the code of the query that 'sometimes' generates the blank column.

léa · Answer

Thank you for the answer.&nbsp;I use Power Query only to retrieve data from workbooks in a folder on the users' computers. They enter the file path in a cell.That's the reason why I was thinking of changing for only VBA. this is the code in PQ (M language)let// Charger la table nommée "nom_utilisateur" depuis le classeur Excel courantUserPathTable = Excel.CurrentWorkbook(){[Name="nom_utilisateur"]}[Content],&nbsp;// Extraire le chemin du dossier depuis la première ligneFolderPath = UserPathTable{0}[nom_utilisateur_colonne],&nbsp;// Construire le chemin complet du fichier ExcelFullFilePath = FolderPath &amp; "\filename.xlsx",&nbsp;// Charger le fichier ExcelSource = Excel.Workbook(File.Contents(FullFilePath), null, true),&nbsp;// Extraire les données de la feuille "sheetname" et les mettre en mémoire pour éviter le verrouillage du fichier gràce à la fonction table.bufferBufferedSheet = Table.Buffer(Source{[Item="sheetname", Kind="Sheet"]}[Data]),&nbsp;// Travailler à partir de la table mise en mémoireSheet_Data = BufferedSheet,&nbsp;// Extraire la première ligneFirstRow = Sheet_Data{0},&nbsp;// Vérifier si la première ligne est complètement vide (null ou vide "")IsFirstRowEmpty = List.AllTrue(List.Transform(Record.ToList(FirstRow), each _ = null or _ = "")),&nbsp;// Supprimer la première ligne seulement si elle est videCleanedData = if IsFirstRowEmpty then Table.Skip(Sheet_Data, 1) else Sheet_Data,&nbsp;// Promouvoir la première ligne restante comme en-têtesPromotedHeaders = Table.PromoteHeaders(CleanedData, [PromoteAllScalars = true]),&nbsp;// Récupérer la liste des noms de colonnesColumnNames = Table.ColumnNames(PromotedHeaders),&nbsp;// Créer une liste dynamique avec le type "any"DynamicColumnTypes = List.Transform(ColumnNames, each {_, type any}),&nbsp;// Appliquer les types dynamiquesFinalTable = Table.TransformColumnTypes(PromotedHeaders, DynamicColumnTypes)&nbsp;inFinalTable

riny_van_eekelen · Answer

Léa​&nbsp;Thanks! I'll do some testing over the week-end.

Forum Discussion

power querry

5 Replies