Forum Discussion
Helper IVAggregating files with different column structures
Hello,
I'm familiar with combining all files in a sharepoint directory. I'm wondering if there's a way to do this for files that may have additional columns (or some columns removed) to others? (in practice over time input reports can have columns added or deleted - I'd like all the new columns (and deleted) to be present in the output table, obviously with empty cells where there is no data.
Please could you tell me if this is possible to automate (e.g. for combining hundreds of input files)
Thank you,
PW
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).
3 Replies
- SamWiseOwl
Super User
Hi PowerWhy
I am using the Folder connector but the SharepointFolder should work the same way.
Each sheet has a different number as its data (1-4) and also varying number of columns (A-H).
They all are in the same named object (Sheet, Table or Rangename) in this case Sheet1.
By default this only returns the column in the first book.
If you make sure THIS sheet contains a copy of every column then the matching columns from all sheets will be returned. Maybe make a dummy master sheet and update that one.
- SamWiseOwl
For fun I tried doing it a different way.
I created a table using Enter Data to name all the columns:
I then appended that into the Transform Sample file and promoted this row to be the new headers
This then applied to all the appended sheets and because all the columns were present it brought through all the data.
- danextian
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).
