Aggregating files with different column structures

Hi PowerWhy 

If you use the combine feature in the gui, the columns being expanded will always depend on the sample file which is usually the first one in the folder.

In the screenshot below, Transform file function transform the sample file and Table.ColumnNames creates a list of column names  of that transformed files which will be used to identify the columns to be expanded in the current applied step.

If you go a step back from Expanded Table Column1, you will see a list of tables to be expanded. The goal is to get the column names of all those tables, remove the duplicates and feed the list to the expand step.

Insert a step in before Expanded Table Colum1 or whatever the name is in your query and write this formula

= let 
// #"Removed Other Columns1" refers to the name of the previous applied step
//[Transform File] is the name of the table of columns in the previous step
tablecolumns = #"Removed Other Columns1"[Transform File],
columnnames = List.Transform( tablecolumns,  Table.ColumnNames ) ,
combinedlist = List.Combine(columnnames)
in
List.Distinct(combinedlist)

As a new step has been inserted befor Expanded Table Column1, the step being referenced will also change causing an error. Change AllColumns back to #"Removed Other Columns1"

Please note that this will be an extra overhead to your ETL process and this can slow down the refresh depending on the number of files, their sizes and the location (SharePoint folder connection is slower than a OneDrive for Business folder although they use the same connector, connecting  to a local folder is of course a lot faster).