Agentic 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: 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. 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 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. 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 Part II - https://share.synthesia.io/509a6433-77ed-4e88-b49b-4cec3e705f39 Part III - https://share.synthesia.io/40b58940-2f5b-4b90-bdd3-ba8a9afea4b6Fabric Job Registry
Fabric Job Registry An ETL orchestration and management platform built with the Microsoft Fabric Extensibility Toolkit. What problem is being solved Organizations running ETL workloads in Microsoft Fabric often hit common orchestration gaps as pipelines scale beyond "one item, one schedule": Job VisibilityKnowing what is scheduled in a Fabric environment is not an easy task at the moment, there is no central place to see all jobs and when they run. Dependency management Production workflows include real dependencies like "Job B starts only after Job A succeeds" and exception paths like "Run Job C if Job D fails." Managing these relationships manually is error-prone and hard to maintain as job counts grow. Template reusability Teams repeatedly recreate similar pipelines with only parameter variations. Without a template library, this often turns into copy/paste and drift, which increases maintenance overhead and inconsistency. Fabric Job Registry addresses these pain points with a Fabric-native experience: multi-schedule orchestration, dependency-aware execution, and a reusable template library all backed by Fabric platform APIs. Features Job Registry Multi-schedule support (up to 25 schedules per job) Define multiple schedules for a single job (minute/hour/day/week/month patterns), with timezone support using IANA timezone IDs and configurable start/end dates. Makes use of Fabric's built in Job Scheduler API Advanced dependency management Model job relationships using dependency types: Completion: start after another job finishes (regardless of outcome) Success: start only if another job succeeds Failure: start only if another job fails (exception handling workflows) Dependency-triggering automation Automatically creates Fabric Activators to monitor job completion events and trigger downstream jobs in real time. Operational controls Central management for schedules, notifications, and to enable/disable jobs. Template Manager Pipeline template library Store full pipeline definitions (Fabric pipeline JSON format) as reusable templates. Parameterization Define template parameters (string, int, bool, array, object), with required/optional handling. Organization + governance Categorize templates (ingestion, transformation, orchestration, export, custom) and maintain version tracking to reduce drift. Fast job creation When creating a new job, select a template and provide parameter values. The system generates the complete pipeline definition automatically. Dashboard Monitoring & observability: KPI metrics, execution history, and centralized visibility across jobs Dependency visualization: dependency canvas/DAG view for understanding orchestration flow Alert management: surface failures, missed schedules, and dependency chain issues API integrations Fabric Platform APIs The workload uses a unified FabricPlatformAPIClient wrapper to interact with core Fabric capabilities, including: Workspace API: list workspaces and enumerate items Items API: create pipelines programmatically and retrieve item metadata Job Scheduler API: create/update/delete up to 25 schedules per item Data Pipeline API: trigger on-demand runs and monitor status All calls follow consistent patterns for authentication, scope selection, and retry logic. Fabric Real-Time Intelligence Activators: auto-generated per dependency relationship to trigger downstream work Event Streams: route job completion events for downstream processing and observability View the video submission here: https://youtu.be/t49N139SbOY View the (Very much a work in progress) source code here: https://github.com/taylorsamy/FabricJobRegistry
Cannot change Data Source of Power BI Report built with Direct Query mode
Hello friends, We have a requirement to redistribute same Power BI Chart (PBIX) to multiple customers (complete different environment with different databases of each customer). Meaning, we can't create PBIX file using 'Import' connectivity mode because we can't let data of development environment be present in PBIX file. So, we are thinking that we can create a master PBIX file using ‘Direct Query’ connection mode which would be calling a SP to get data. And we will distribute that PBIX file to all customers and they can further point that PBIX file to their DB (SQL DB on their Premises). Meaning, after creating and delivering PBIX files to customer, the customers will still have to change the data source (connection string of Data source) to point it to their DB. And we will like to have a programmatic way to do so. We first checked the Report Portal URL and went to ‘Manage > Data Sources’ option and observed that Connection String was disabled there. Meaning, we couldn’t change it from there. Then, we thought to do it Programmatically using REST APIs of Data sources. But, REST API also couldn’t do that if we are changing Connection string of the Data source (it returns Bad request response). So, we are landing to conclusion that ‘We cannot change the connection string of a data source after the PBIX file is created from Power BI Desktop’. Can someone please help me out on this? I could figure out two ways to change Data source connection after the PBIX file is created from Power BI Desktop using Direct Query mode. One is to open the PBIX in Power BI Desktop and navigate to 'Option and Settings > Data Source Settings'; but we have hundreds of PBIX files which has to be dilievered to 8-10 different customers. So, opening all files in PBI Desktop and change data source according to each customer would be time consuming. Other way is to go and modify data of 'DataModelDataSource' table of Report Server. But this is also not a clean way, because things are saved in encrypted format there. I would like to know if there are any others ways or not. Also, if anyone have idea if Microsoft is planning to bring/extend API to change data source of a PBIX (at least for Direct Query mode).
