Combine multiple files from SharePoint folder with different number of columns/structure

[anns277]

Hello, 

I have multiple excel files in the SharePoint folder which all have a similar structure like below:

ID | Client | Resource | Resource ID | 30/12/2019 | 6/01/2020 | 13/01/2020 | 20/01/2020 | ...

All files have around 20 columns that are not changing and the other columns with start of week date ( every sheet has different dates depending on project and might change in the future). 

Is there a way to load all files at once from SharePoint Folder into one table without having errors in Transform File? 

[Jimmy801]

Hello @anns277 

you can read from the folder... in case filter the files you really need.

Then click on the top right of the content-column

Go through the procedure. Power Query will create a function to read from your files without applying column references (i hope that the sheet names are the same, otherwise this approach will not work I suppose).  The only thing you have to change is the second last step, where the tables are expanded according to the first files.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[anns277]

Hi @Jimmy801 ,

Thank you for your help.

The procedure you described gave me below table:

Is there a way dates from the first file will appear as a new columns? 

Thanks

[Jimmy801]

Hello @anns277 

what you mean by columns from the first file? If your files are all structured the same way everything should be fine. What I don't understand is why there are some anonymous columns and one named columns. But anyway... it seems that your files are not the same, and then you will have hard time to combine them easily. But without seeing your files it's difficult to help you

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy