How to Dynamically Load Only the Non-Empty Tables Into the Data Model?

[RokuCap]

I have a few Excel workbooks I want to load data from every month. Each workbook will contain over 20 worksheets, each worksheet with one table. The worksheets should be loaded in their own query and not combined/appended with other workbooks. However, not every table will have data; some will be empty tables.

Power BI gives you the option to manually select which tables to load via the 'Navigator' menu but is there a way to dynamically load only the non-empty tables? If so, would this be achieved in DAX or M? As checking each table every week across multiple workbooks would be a very time consuming task.

For example, let's assume an Excel workbook with 8 Tables named "Table1" to "Table8" on separate worksheets also labelled "Table1" to "Table8". Only Table3, Table4 and Table 5 have data. The empty tables should not be loaded into the Data Model to avoid clutter.

Thanks in advance!

[v-shex-msft · Accepted solution]

HI @RokuCap,

In my opinion, I think these operations should suitable to processed in Power query.

If these files are stored in the folder, you can use folder connector to get data from that. Then you can add filters on different fields(they mean the file properties) to the filters connector result to filter not match records, and load data form remained files.

Regards,

Xiaoxin Sheng

[RokuCap]

Thanks Xiaoxin. I'll give the folder connector a shot. I'm still learning Power Query so I might repost this over there. But you've given me a starting point!

[RokuCap]

Is this question better suited for the Power Query forum? If so, am I allowed to repost over there?