Splitting SharePoint Excel Files into Multiple Tables in Power BI

[Lora9999]

Hello.

How can I load multiple Excel files from SharePoint into Power BI and split them into two separate tables?

For example, I want to combine A-1, A-2, and A-3 into Table 1, and B-1, B-2, and B-3 into Table 2.

[v-mdharahman · Accepted solution]

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.

[danextian]

Hi @Lora9999 

What are A-1, A-2, and A-3? What is the logic behind identifying what they are?

[Lora9999]

Each one is a file name, and all are Excel files.

[danextian]

I would connect to the files via the Sharepoint.Files or Sharepoint.Contents function and filter the files based on the filename - one for those that start with A and another one for B. Then use the combine files feature for each query.