rsaprano
Most Valuable Professional
Most Valuable ProfessionalAgentic Excel to Fabric Migration Workload
🎯 A. The Problem
Every mid and large organisation has hundreds - sometimes thousands or more - of Excel-based business processes driving critical reporting in areas such as finance.
These teams know they *should* move to centralised platforms like Microsoft Fabric and Power BI, but face a brutal reality:
Every mid and large organisation has hundreds - sometimes thousands or more - of Excel-based business processes driving critical reporting in areas such as finance.
These teams know they *should* move to centralised platforms like Microsoft Fabric and Power BI, but face a brutal reality:
1) The skills gap is real: Generic Power BI training doesn't translate into building production-grade, enterprise-compliant analytics solutions. Users go on courses, try to apply the concepts to their own data, get stuck, and go back to Excel. Many organisations haven't even tried to upskill their analysts in Fabric yet, keeping data engineering activities with centralised Data/BI teams.
2) Technical debt creeps in fast. The reports that do get built are often rejected by Technical Review boards/COE teams for broader rollout because they fail to meet corporate Governance standards - data models are wrong, DAX is messy, governance and security requirements aren't met. That 100-page compliance document? Nobody knows how to translate it into Power BI or Fabric capabilities.
3) There's no bandwidth in centralised teams: Data engineering teams have massive backlogs, headcount is frozen, and business users can't wait months for a migration that the central team will never get to.
Moreover, Gen AI alone doesn't solve it. It doesn't create data literacy, it exposes the challenges that come from a lack of it! Tools like Copilot give generic advice - they have no context about your specific business process, data structures, corporate requirements, or the nuances buried inside your spreadsheets. AI without context is just noise.
2) Technical debt creeps in fast. The reports that do get built are often rejected by Technical Review boards/COE teams for broader rollout because they fail to meet corporate Governance standards - data models are wrong, DAX is messy, governance and security requirements aren't met. That 100-page compliance document? Nobody knows how to translate it into Power BI or Fabric capabilities.
3) There's no bandwidth in centralised teams: Data engineering teams have massive backlogs, headcount is frozen, and business users can't wait months for a migration that the central team will never get to.
Moreover, Gen AI alone doesn't solve it. It doesn't create data literacy, it exposes the challenges that come from a lack of it! Tools like Copilot give generic advice - they have no context about your specific business process, data structures, corporate requirements, or the nuances buried inside your spreadsheets. AI without context is just noise.
The result? Critical financial processes stay trapped in ungoverned, unauditable spreadsheets, exactly where regulators don't want them!
💡 B. The Solution
The Excel-to-Fabric Migration Agent is a multi-agent system built as a Fabric Workload that takes an Excel workbook as input and automates the entire journey into Microsoft Fabric, from understanding the business logic to deploying a fully governed, medallion-architecture data solution.
In this scenario, Excel isn't just a data source, it's a specification document. Business users have already captured their data structures as tables and their business logic as formulas. The agent reads and understands this context, then uses it to design, build, and deploy the equivalent solution in Fabric, with the user validating at every step.
🔁 C. How it works:
1. Upload & Analyse: The user uploads an Excel workbook. The agent parses the XML structure, classifies sheets/data tables and cell blocks by role (Input, Lookup, Calculation, Output), and translates formulas into plain-English logic. It produces a human-readable Standard Operating Procedure (SOP) for the user to validate and highlight where
2. Architecture Design: The agent designs a three-tier medallion architecture (Bronze → Silver → Gold lakehouses) and writes Spark SQL Materialized Lake Views (as an example) that replicate the Excel calculation logic, handling format mismatches, implicit joins, and cross-sheet references automatically.
3. Fabric Deployment: Lakehouses are created, CSVs uploaded, delta tables loaded, notebooks deployed, and OneLake shortcuts configured, all via Fabric REST APIs. The user sees live deployment progress with status indicators.
4. Power BI Modeling: A star schema semantic model is created with DAX measures, and a comprehensive SOP documentation workbook is generated with Power Query connections to every lakehouse table, so outputs can still be reviewed in Excel via a live OneLake connection.
Human-in-the-loop throughout.: The agent pauses at every phase boundary for user validation; it never deploys without explicit confirmation.
🏗️ D. Architecture & Tech Stack
The solution consists of the following components:
1. AI Context Engineering: Context documents (markdown files) containing reuqirements, best practices and instructions for each agent.
2. Agent Framework: Multi-agent orchestration with 4 specialised worker agents
1. AI Context Engineering: Context documents (markdown files) containing reuqirements, best practices and instructions for each agent.
2. Agent Framework: Multi-agent orchestration with 4 specialised worker agents
3. Workload Frontend: Built using the Fabric Extensibility Toolkit (React + Fluent UI)
4. Excel Parsing capabilities: Code to save the XLSX file as XML files for analysis
5. Fabric Lakehouses: Represented in a Medallion pattern (Bronze / Silver / Gold)
6. Fabric Notebooks: Business Logic included e.g. as Pyspark or Spark SQL Materialized Lake Views (MLVs)
7. Fabric Deployment: Via Fabric REST APIs, OneLake DFS API, Lakehouse Table Load API
8. Semantic Layer Build: Automatic Power BI semantic model build where required (TMDL format) with DAX measures
9. Documentation: Auto-generated SOP Excel workbook with Power Query M connections to OneLake Lakehouses.
✨ E. Key Features
1). 📊 Excel-native starting point: Meets users where they are. No need to describe requirements from scratch as the agent reads the workbook and extracts the existing process.
2) 🏛️ Enterprise-grade architecture: Automatically designs medallion lakehouses with proper cross-lakehouse references, format normalisation, and join resolution.
3) 🔁 Formula-to-SQL/Python translation: Converts Excel calculation logic (e.g. SUMIFS, VLOOKUPs, IF statements) into equivalent SQL/Python Code
4) 🚀 One-click deployment : Creates lakehouses, uploads data, deploys notebooks, and configures shortcuts through Fabric APIs; no manual setup required.
4) 🚀 One-click deployment : Creates lakehouses, uploads data, deploys notebooks, and configures shortcuts through Fabric APIs; no manual setup required.
5) 📋 Auto-generated SOP: Produces complete Standard Operating Procedure documents with process steps, validation checks, and colour-coded sheet references.
6) 🔒 Human-in-the-loop: User checkpoints at every phase boundary ensures the agent never deploys without explicit validation.
7) 📈 Power BI ready: Outputs a star schema semantic model and an Excel workbook with live Power Query connections to the deployed lakehouse tables.
The solution video is split into three parts:
Part I - https://share.synthesia.io/ef56645f-54ed-4da5-9fa5-29446948abbc
No RepliesBe the first to reply