Forum Discussion
Excel mirrorring fabric 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.
Super UserHi 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.