Excel mirrorring fabric table

[Msoede]

Hi,

Hopefully someone know a bit more about the following. Would it be possible to have multiple files by different usesrs on sharepoint mirrorring to one and the same table in Fabric? I envision a situation in which managers edit a file and that the new rows are mirrorred to the same table in Fabric.

regards,

[Msoede]

Dear Burakkaragoz, many thanks for your solutions. We will investigate further which route we will take!

[burakkaragoz]

Thank you so much for your kind words! I’m really glad I could help. If you have any more questions or need further assistance as you decide on your solution, feel free to reach out anytime. Wishing you the best of luck with your project and the route you choose!

[burakkaragoz · Accepted solution]

Hi @Msoede ,
Great follow up and thanks for clarifying your challenge. Since your Excel files are spread across multiple SharePoint sites (not just one folder), there are a couple of ways to automate consolidation into a single Fabric table, without relying on manual VBA scripts:

1. Use SharePoint Dataflows (with multiple sites):

• In Power BI Dataflows (or Fabric Dataflows Gen2), you can connect to multiple SharePoint sites by adding each site as a separate data source.
• For each site, connect to the relevant Document Library and select the Excel files you want.
• Combine (append) the data from all sources within your dataflow using Power Query’s “Append Queries” feature.
• Apply your merge/deduplication logic at this stage.
• Output the final table to your Fabric Lakehouse table (or any other Fabric destination).

2. Leverage a Notebook/Script for Automation:

• In a Fabric Notebook, use the Python or Spark “SharePlum”/“Office365-REST-Python-Client” libraries to authenticate and connect to multiple SharePoint sites programmatically.
• Loop over all sites and folders, read in all the Excel files, and merge their content.
• Perform any row-level comparison or deduplication logic as needed.
• Write the combined result directly into your Fabric Lakehouse or Data Warehouse.

Example Notebook Logic:

• List all SharePoint site URLs in a config array.
• Authenticate once and iterate through each site, reading Excel files.
• Use pandas (Python) or Spark for merging and cleaning.
• Save/appends to the Fabric table.

3. Centralize File Drop-off (if feasible):

• If you can control the workflow, consider having all managers drop their Excel files into a centralized SharePoint folder, even if accessed from different sites.
• This will simplify the data ingestion process, as the original suggestion would then work out-of-the-box.

Summary Table:

Approach Pros Cons

Dataflow (multi-site)	No code, UI-based, easy monitoring	Needs setup for each site, may be slow
Notebook/Script	Fully automated, scalable, flexible logic	Requires coding, maintenance needed
Centralized Drop-off	Simplest ingestion, fewer connections	Requires process change for users

If you want, I can share a sample script for connecting to multiple SharePoint sites and merging Excel data automatically in a Fabric Notebook.

Let me know if you’d like further details or step-by-step guidance for one of these solutions!

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[Msoede]

Thanks for your advice. Only challenge is that the files are not stored on one location on sharepoint but on multiple websites. I ended up with using some vba script in a file that writes down the data to a sql server table. 

[Vinodh247]

Did you try this step already? this shoudl work for you

1. Managers Excel Files in SharePoint Folder

2. Dataflow Gen2 / Notebook -> Reads all files from the folder

3. Merge Logic -> Filters new rows or compares with previous state

4. Append to Fabric Lakehouse Table