Splitting SharePoint Excel Files into Multiple Tables in Power BI

Hi Lora9999,

Thanks for reaching out to the Microsoft fabric community forum.

Since you’re working with multiple Excel files stored on SharePoint and you want to combine specific ones (A-1, A-2, A-3 into Table 1, and B-1, B-2, B-3 into Table 2), the good news is that Power BI can handle this using Power Query with some filtering logic based on file names.

Here’s a general approach you can follow:

* Connect to your SharePoint folder. In Power BI Desktop, go to Home -> Get Data -> SharePoint Folder. Then enter the SharePoint site URL, and authenticate if prompted. This will list all files in the site. Filter to only include the relevant Excel files (A-1, A-2, A-3, B-1, B-2, B-3).

* In Power Query Editor, use the Name column to filter files. Create one query that filters for A-1, A-2, A-3 (e.g., by checking if the name starts with “A-”) this will become Table 1. Duplicate that query and change the filter to pick up B-1, B-2, B-3 (e.g., name starts with “B-”) this will become Table 2.

* Combine files within each query to merge the content of each group. Make sure the structure (sheet names, columns, etc.) is consistent across the files within each group.

Lastly load each query as a separate table in Power BI.

I would also take a moment to thank danextian, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.