Power Query by Default Excludes First Blank Column

I have researched this down the rabbit hole long enough without any answers. In Excel I created a Power Query Get Data from Sharepoint Folders using the Contents method. There are about 10 files wher...

excel

power bi

SergeiBaklan

Mar 24, 2025

With Excel is practically the same, the only you have to define sheet name to combine

let

    SheetToSelect = "Test",
    NumberOfColumns = 4,

    NewNames = List.Zip( {
        List.Transform( {1..NumberOfColumns-1}, (q) => "Column" & Text.From(q) ),
        List.Transform( {2..NumberOfColumns}, (q) => "Column" & Text.From(q) )
    } ),

    Source = Excel.Workbook(Parameter1, null, true){[Item=SheetToSelect,Kind="Sheet"]}[Data],

    n = Table.ColumnCount(Source),
    AdjustColumns =
        if n < NumberOfColumns
        then Table.RenameColumns( Source, NewNames )
        else Source
in
    AdjustColumns

heylookitsme

Iron Contributor

Mar 24, 2025

SergeiBaklan,

Thank you for your response. I should have stated what file types I was importing. They are .xlsx files.

I will try the same query modifying it from csv to xlsx and see if that works.

SergeiBaklan

Diamond Contributor

Mar 24, 2025

With Excel is practically the same, the only you have to define sheet name to combine

let

    SheetToSelect = "Test",
    NumberOfColumns = 4,

    NewNames = List.Zip( {
        List.Transform( {1..NumberOfColumns-1}, (q) => "Column" & Text.From(q) ),
        List.Transform( {2..NumberOfColumns}, (q) => "Column" & Text.From(q) )
    } ),

    Source = Excel.Workbook(Parameter1, null, true){[Item=SheetToSelect,Kind="Sheet"]}[Data],

    n = Table.ColumnCount(Source),
    AdjustColumns =
        if n < NumberOfColumns
        then Table.RenameColumns( Source, NewNames )
        else Source
in
    AdjustColumns

Forum Discussion

Power Query by Default Excludes First Blank Column