Forum Discussion
Solved
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 where the data is in the same format, columns match sheets are all the same name, etc.... FYI, None of them are setup as Tables for reasons I won't go into. They just can't.
When I import the files 5 of the files have data in column A while 5 do not. When I import the data the files that do not have data in column A Power Query is automatically removing those columns from those sheets. Which then screws up the column order when the data is appended and loaded to the table. How can I force Power Query to bring in Column A even if some files column A are blank?
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
If to assume you combine csv files, for them Power Query removes blank columns before first one with data. And after last one with data if number of columns is not specified. If you check Transform Sample File query, it could look like
let Source = Csv.Document(Parameter1,[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]) in Sourcewhere options, in particular number of columns, are automatically taken from the analysis of first sample file. Resulting table by default has headers Column1, Column2, etc.
We may modify that query so if resulting table has one column less, headers will be Column2, Column3, etc. In combined table columns will be arranged by their column names, thus we will have correct order of columns. Modified Transform Sample File query could be
let 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 = Csv.Document(Parameter1,[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]), n = Table.ColumnCount(Source), AdjustColumns = if n < NumberOfColumns then Table.RenameColumns( Source, NewNames ) else Source in AdjustColumnsDon't forget to remove Columns=4 option in Csv.Document(), that's the key. And use your actual number of columns instead of 4.
Most probably similar could be done if you have another setup.
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.
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
I found a solution albeit maybe not the best one. I created two separate queries, modified the one that had the additional column and removed it. Then I appended those two queries together.
