Combine CSV files in folder with different column counts

[Aidan_Wang]

I am using power query to import multiple CSV files from SharePoint document. But, my files contain a different count of columns:

Most files:

Column A	Column B	Column C	Column D	Column E	Column F

But some files:

Column A	Column B	Column C	Column D	Column G	Column H	Column E	Column F

As PQ uses the first file as a sample file, the transform sample file only contains 6 rows. In the final result, the extra two columns (G&H) do not show up but I can't find codes that delete these two columns. I thought Column G&H should show up with most rows are null.

Why did these two columns disappear and how can I make all columns available in the result?

Here is my M code:

let
    Source = SharePoint.Contents("SITE ADDRESS",[ApiVersion = 15]),
    Documents = Source{[Name="Documents"]}[Content],
    #"Database" = Documents{[Name="Database"]}[Content],
    #"Transaction Detail" = #"Database"{[Name="Transaction"]}[Content],
    #"Expanded Content" = Table.ExpandTableColumn(#"Transaction", "Content", {"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}, {"Content.Content", "Content.Name", "Content.Extension", "Content.Date accessed", "Content.Date modified", "Content.Date created", "Content.Attributes", "Content.Folder Path"}),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Expanded Content", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content.Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),

I really appreciate any help you can provide.

[BA_Pete]

Hi @Aidan_Wang ,

It's the Transform From Sample File query that you will need to edit. The code you've provided is for the output query after the sample transformations have been completed.

You should see a helper query folder in your queries list that contains the query I've mentioned above.

Pete