Forum Discussion
Excel mirrorring fabric table
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,
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.
5 Replies
Did you try this step already? this shoudl work for you
Managers Excel Files in SharePoint Folder
Dataflow Gen2 / Notebook -> Reads all files from the folder
Merge Logic -> Filters new rows or compares with previous state
Append to Fabric Lakehouse Table
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.
Dear Burakkaragoz, many thanks for your solutions. We will investigate further which route we will take!
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!
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.
