Dataflow Gen1 to Microsoft Fabric Migration: The Complete Enterprise Playbook

Why Dataflow Gen1 Migration Cannot Wait

Dataflow Gen1 was introduced as Power BI's self-service ETL tool — a way for business analysts to prepare data without writing code. It worked well for departmental use cases, but enterprises quickly outgrew its limitations: no staging layer, limited orchestration, dataset-only output, and gateway-bound connectivity.

Microsoft Fabric changes the game. Dataflow Gen2 is not just an upgrade — it is a re-architecture. Gen2 dataflows run on Fabric compute, output to any Fabric destination (Lakehouse, Warehouse, KQL Database, Azure SQL), support staging lakehouses for intermediate transformations, and integrate natively with Fabric pipelines for enterprise orchestration.

The business case is straightforward: Gen1 dataflows will eventually lose support, Gen2 offers 2–5x better performance through query folding and fast copy, and Fabric's unified capacity model eliminates the separate Power BI Premium Per User or Per Capacity licensing overhead. Organizations that migrate early gain access to Copilot in Data Factory, enhanced monitoring, and the full Fabric governance stack through Microsoft Purview integration.

Phase 1: Migration Assessment and Inventory

Every successful migration starts with a complete inventory. EPC Group's assessment framework catalogs every Gen1 dataflow across your tenant, classifies migration complexity, and builds the dependency map that drives sequencing.

Dataflow Inventory Checklist

• Total dataflow count — Use the Power BI REST API (Groups/Dataflows endpoint) to enumerate all dataflows across all workspaces. Do not rely on manual workspace audits.
• Data source inventory — Catalog every data source connection: SQL Server, Oracle, REST APIs, SharePoint lists, Excel files, OData feeds. Flag any sources using on-premises data gateways.
• M query complexity scoring — Parse each dataflow's M queries for custom functions, nested let expressions, error handling patterns, and Value.NativeQuery calls. Score each as Low (direct migration), Medium (minor refactoring), or High (rewrite required).
• Downstream dependency mapping — Identify every Power BI dataset, report, and dashboard that consumes each dataflow. This determines migration sequencing — you cannot migrate a dataflow until its consumers are ready.
• Refresh schedule and SLA documentation — Record current refresh frequencies, windows, and business SLAs. Gen2 refresh times may differ, so baseline metrics are essential for post-migration validation.
• Incremental refresh configurations — Document every dataflow using RangeStart/RangeEnd parameters. These require specific migration handling in Gen2.

EPC Group delivers this assessment as a 2-week engagement with a detailed migration plan, effort estimate, and risk register. For organizations with complex Power BI environments, the assessment alone prevents costly surprises during execution.

Phase 2: Dataflow Gen2 Architecture Design

Gen2 is not a 1:1 replacement for Gen1. The architecture is fundamentally different, and a successful migration requires deliberate design decisions before writing a single M query.

Key Architecture Decisions

Phase 3: M Query Conversion and Testing

The core migration work is converting M queries from Gen1 to Gen2. While most M code is compatible, several patterns require attention.

Common M Query Migration Patterns

• Linked entities — Gen1 supports linked entities (referencing another dataflow's output). Gen2 replaces this with staging lakehouses. Convert linked entity references to Lakehouse.Table() calls pointing to the staging lakehouse.
• Computed entities — Similar to linked entities, computed entities in Gen1 are replaced by reading from the staging lakehouse in Gen2. Refactor computed entity queries to read from Delta tables.
• Custom connectors — Gen2 supports a growing but not yet complete set of connectors. Audit your custom connectors against the Gen2 compatibility list. For unsupported connectors, use a Fabric pipeline with a Web activity or Azure Function as a bridge.
• Gateway-dependent sources — Gen2 supports on-premises data gateways, but the configuration differs. Re-bind gateway connections in the Gen2 dataflow settings. Test connectivity before migrating M queries.
• Error handling patterns — M queries using try/otherwise patterns and custom error tables migrate directly. However, Gen2's error handling in the output configuration (row-level error capture) provides a better alternative. Consider refactoring error handling to use native Gen2 capabilities.

Testing Methodology

EPC Group uses a three-stage testing protocol for every migrated dataflow: (1) row count validation — Gen2 output row count must match Gen1 within 0.01%, (2) hash-based data comparison — generate SHA-256 hashes of key columns in both Gen1 and Gen2 output and compare, (3) refresh performance benchmarking — Gen2 refresh time must be within 120% of Gen1 baseline (most achieve 50–80% of Gen1 time). Document results in a migration validation matrix and obtain business owner sign-off before cutover.

Phase 4: Incremental Refresh Migration

Incremental refresh is the most complex migration topic. In Gen1, you configure RangeStart and RangeEnd parameters, and Power BI manages partition creation automatically. In Gen2, the approach depends on your output destination.

For Lakehouse destinations, EPC Group implements a sliding-window pattern: the M query accepts StartDate and EndDate parameters, the Fabric pipeline passes dynamic date values on each scheduled run, and the Lakehouse table is configured for merge (upsert) rather than overwrite. This gives you true incremental loading with full control over the refresh window.

Phase 5: Capacity Planning and Performance Optimization

Fabric capacity planning for Dataflow Gen2 workloads requires understanding CU consumption patterns. Unlike Gen1, where refresh capacity was part of your Power BI Premium SKU, Gen2 dataflows compete for CUs with every other Fabric workload in the same capacity.

Capacity Sizing Guidelines

These are starting estimates. Actual CU consumption depends on data volume, transformation complexity, connector type (fast copy vs. standard), and concurrency. EPC Group's capacity planning engagement includes a 2-week instrumented pilot with detailed CU consumption analysis and right-sizing recommendations.

Performance Optimization Checklist

• Enable staging for dataflows processing over 1 million rows to leverage fast copy
• Maximize query folding — push filters, column selections, and joins to the source system
• Avoid Table.Buffer() and List.Buffer() in M queries — these prevent query folding and consume memory
• Use native query (Value.NativeQuery) for SQL sources to ensure server-side execution
• Schedule high-volume dataflows during off-peak hours to avoid CU contention
• Monitor using the Fabric Capacity Metrics app — set alerts for CU utilization above 80%

Cutover Strategy: Parallel Run and Decommission

EPC Group never recommends a big-bang cutover. Instead, we use a parallel-run approach: Gen1 and Gen2 dataflows run simultaneously for 2–4 weeks, outputs are compared daily using automated validation scripts, and downstream consumers are switched in waves. Only after all consumers are migrated and validation passes for 5 consecutive business days do we decommission the Gen1 dataflow.

This approach adds capacity cost during the parallel period, but it eliminates the risk of data disruption. For regulated industries — healthcare, financial services, government — where data accuracy SLAs are contractual, parallel-run is not optional. It is a requirement.

Frequently Asked Questions