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

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!