undefined

[jacksonlam]

I have a excel worksheet with several excel tables and new tables will be added from time to time. Is it possible to configure Power BI query to automatically detect and connect to new tables as data source?

[Jimmy801]

Hello @jacksonlam 

yes, you can. Use the function Excel.Workbook(File.Contents(yourfilename),null, true) to create a table with your Excelobjects. Apply a filter if needed on this table. Apply a function to the table if you would like to transform the data or use Table.ExpandTableColumn to extract your data.

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

[edhans]

How would you get those new tables to automatically expand to new queries though @Jimmy801 - I would assume the tables would not be the same format, otherwise, they would just be additional rows in existing tables.

[Jimmy801]

Hello @edhans 

for sure they have to have the same format, or at least following the same logic to extract the data. I had one time a case were they maintained assetts in one big excel-file, and whenever a assett was created from a template and by adding a new sheet, this new data needed to be integrated in a power-pivot database. 

BR

Jimmy

[Greg_Deckler]

@jacksonlam - That's a tall order. I am going to say no but I'm more than willing to be proven wrong by someone, perhaps @ImkeF or @edhans . Here is a similar thread:

https://community.powerbi.com/t5/Desktop/Dynamically-generate-query-tables/td-p/20519

I don't see a solution there.