Connect to multiple, differing structured excel files in a SharePoint folder via PowerBI

[bblackwell3]

Hey All. I have a SharePoint folder that contains multiple Excel spreadsheets. Some of the spreadsheets have multiple tabs and are not in a consistent structure. For example, one file may have costs associated to budget items, while another sheet is related to number of resources per team over a 12 month period. So combining via Power Query is not an option.

Is there a way to directly connect to these data sources without moving into Sharepoint lists? The current process is downloading the file locally and using it within the PowerBI model. Future state is to setup automated refresh on the PowerBI service

[v-karpurapud]

Hi @bblackwell3 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-karpurapud]

Hi @bblackwell3 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hi @bblackwell3 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud · Accepted solution]

Hi @bblackwell3 

Thank you for reaching out to the Microsoft Fabric Community Forum.
 

As correctly suggested by @lbendlin , Power BI handles files with a consistent structure efficiently using the "Combine Files" functionality within Power Query.
 

However, when dealing with Excel files that have varying structures, automatic combination is not supported. In such scenarios, it is recommended to implement a custom function in Power Query. This approach allows you to dynamically apply different transformation logic based on the file name, sheet name, or structure, thereby reducing manual effort and enabling automated data refresh in the Power BI Service.
 

For more detailed guidance on implementing custom functions, please refer to the official Microsoft documentation:

Using custom functions in Power Query - Power Query | Microsoft Learn
Power Query SharePoint folder connector - Power Query | Microsoft Learn

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank You!
 

[lbendlin]

In Power BI (Power Query) you have two options

1.  Your files (and sheets) have the same structure - you can automatically combine them into a single query, and it will work even when files are added or removed

2.  Your files (and sheets) have all different structures - you will need to manually create queries, and will need to add new queries manually when files are added.