Most efficient way to load this kind of scenario?

[Nozama]

I have a sharepoint folder where there are multiple excel workbooks - all with multiple workshsheets inside them.

Book1: Sales, Colour, Country, Product

Book2: Sales, Colour, Country, Product

...

BookN: Sales, Colour, Country, Product

I wish to create 4 tables in my report that basically are of this form:

Book0: Sales (all sales combined)

Book0: Colours (all colours combined)

Book0: Country (all countries combined)

etc.

1. I have managed to link to sharepoint folder and import all files
2. I have managed to write queries for all my tables. That is fine.

What I am wondering is that each of my queries, loads all the workbooks and all the sheets, while I only select one sheet from each of them. Is this slowing my report down? Is there a way for Power Bi to just load all workbooks at once rather than in each query?

[johnt75]

There will be a performance hit for loading all the data in multiple times, but that will only affect the time it takes to refresh the data. End users will not notice any performance issues when viewing and interacting with reports.

Depending on when the data refreshes you might not need to do anything. If the data only refreshes out of hours, when noone will be looking at the report anyway, then does it really matter if the data refresh takes e.g. 20 minutes to run instead of 10 minutes ?

If you are refreshing multiple times throughout the day and it is important to execute the refresh as quickly as possible, you could set up a dataflow to do the heavy lifting of reading and combining all the sheets from all the workbooks and then set up individual queries which read from the dataflow and extract the colours, countries etc.

[v-ssriganesh · Accepted solution]

Hello @Nozama,
Thank you for sharing your scenario with the Microsoft fabric Community.

You're absolutely on the right track by connecting to the SharePoint folder and setting up queries for each table. Regarding your concern yes, you're right that repeatedly loading all workbooks and all worksheets in each individual query can impact performance, especially when scaling up.

To improve performance and avoid redundant data loading, you can centralize the workbook and worksheet extraction in a single base query, then reference this base query in your four specific tables. This way:

• The connection to SharePoint and the combination of files happens only once.
• You filter each required worksheet from the combined dataset in referenced queries.
• Power BI handles the data more efficiently through query folding (when applicable), and it reduces the number of repeated steps.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[Nozama]

Thank you for your reply. This is indeed a good solution, however, I am wondering if it really is a red herring based on information here. Referencing Power Query queries - Power BI | Microsoft Learn

[v-ssriganesh]

Hello @Nozama,
Thank you for referencing the Microsoft Learn article.

You're right referencing a query in Power Query doesn’t always prevent re-evaluation. However, in scenarios involving SharePoint folder connections, creating a staging (base) query is still a recommended practice for performance optimization.

• If your base query handles all heavy steps (like combining files and filtering by sheet name), and your final queries simply reference that structured output, you avoid re-reading the SharePoint source.
• The performance gain comes from limiting how often Power BI connects to the external data source especially important for folders with many files.

So, while referencing doesn’t cache automatically, proper structuring still reduces redundant processing when working with remote sources.

If your query has been answered, please “Accept as Solution” and Give “Kudos” so others with similar issues can easily find the resolution.
Thank you.