Clean up repeating excel file expansions

[Lukeal]

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?

[v-shex-msft]

HI @Lukeal,

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

[parry2k · Accepted solution]

@Lukeal 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.⚡