Forum Discussion
Clean up repeating excel file expansions
Morning,
I have a folder structure filled with a large number of excel files, all with identical layout but differing data in multiple tabs.
I can easily set up a monitor of the folder to pull in the sheets and expand the tabs into individual tables merged across all the excel sheets,
All this I can accomplish but the UI creates a set of Transform File folders and query for each tab/table combination and it just seems to be a bit redundant. I have no problems with pulling the data in, all the files match and such, it is just irritating that there are like 5 folders/files for every single tab I create a table from. Seems like there should be a cleaner way to merge the individual tabs out into a table for multiple excel sheets? Any ideas?
Anonymous there are tons of solution to handle this where you can have function which take content from a sheet from each excel file and combine it, here are few posts
https://www.youtube.com/watch?v=GvKYUgka_4Y
https://www.youtube.com/watch?v=4fFL7Ok7Ovg
https://www.poweredsolutions.co/2019/04/23/combine-or-append-data-combining-excel-files/
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
2 Replies
- parry2k
Super User
Anonymous there are tons of solution to handle this where you can have function which take content from a sheet from each excel file and combine it, here are few posts
https://www.youtube.com/watch?v=GvKYUgka_4Y
https://www.youtube.com/watch?v=4fFL7Ok7Ovg
https://www.poweredsolutions.co/2019/04/23/combine-or-append-data-combining-excel-files/
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
HI Anonymous,
So you mean your excel files stored similar data structure with different structure sheets (e.g. sheet1 is customer, sheet2 is product, etc...) and you want to extract and combine the same type sheets from different excel files?
If this is a case, I think you need to do some custom with table functions, excel connector, and filters.
Can you please share some dummy data files and expected results? It will help us clarify your requirements and test to create a sample pbix file.How to Get Your Question Answered Quickly
Regards,Xiaoxin Sheng