From Power BI Dataflows to Microsoft Fabric: A Practical Migration Guide

1. Why Move from Power BI Dataflows to Fabric?

Power BI dataflows did a good job as a lightweight ETL layer. Fabric extends that idea into a full data platform. For analytics teams, the main drivers to migrate are:

• Unified storage

  • Dataflows (Gen2) and Lakehouses share OneLake.
  • No more copying data for each dataset; you model once, reuse everywhere.

• Better governance and lineage

  • Lineage across pipelines, notebooks, Lakehouses, and Power BI models.
  • Centralized control over refresh, security, and capacity.

• Flexible compute

  • Use the right engine for the job: dataflows (Gen2), Data Factory pipelines, notebooks, SQL endpoints.
  • Offload heavy transformations from Power BI to Fabric.

• Future-proofing

  • New capabilities (Direct Lake, shortcuts, domains) land in Fabric first.
  • Classic dataflows are unlikely to get major new features.

The goal isn’t “lift and shift everything overnight”. It’s to slowly move your core transformations and storage into Fabric while keeping reports running.

2. Take Inventory: What Are You Migrating?

Before you touch Fabric, you need a clear picture of your current dataflows.

2.1 Classify Your Existing Dataflows

Create a simple inventory (Excel, Power BI, or a Fabric Lakehouse table):

• By purpose

  • Staging (raw ingestion)
  • Business logic (calculated columns, merging tables)
  • Aggregations
  • Data marts

• By usage

  • Number of dependent datasets/reports
  • Whether used in multiple workspaces

• By connectivity

  • On-prem sources (via gateway)
  • Cloud sources (SQL, SaaS APIs, SharePoint, etc.)

• By complexity

  • Number of queries per dataflow
  • Heavy Power Query steps (joins, groupings, custom columns)

You can document refresh dependencies with a simple table:

Focus migration on:

1. Shared dataflows used by many datasets.
2. Dataflows that are slow or fragile.
3. Dataflows that would benefit from Lakehouse storage or Direct Lake.

3. Choose Your Fabric Target: Dataflows (Gen2), Lakehouse, or Warehouse?

In Fabric, you have multiple options to replace a Power BI dataflow. The choice depends on how much engineering you want and what your downstream models need.

3.1 When to Use Dataflows (Gen2)

Use Dataflows (Gen2) when:

• You want to stay in Power Query.
• You’re replacing like-for-like ETL from classic dataflows.
• You need business-user-friendly transformations.

Typical pattern:

• Dataflows (Gen2) ingest and transform.
• Output lands in a Lakehouse table.
• Power BI datasets connect to the Lakehouse.

3.2 When to Use Lakehouse + Pipelines/Notebooks

Use Lakehouse when:

• You need a central, reusable storage layer.
• You have large volumes where parquet/Delta storage matters.
• You want to mix ETL tools (pipelines, notebooks, dataflows).

Pattern:

• Pipelines / notebooks ingest.
• Transformations in Spark or SQL.
• Power BI connects via Direct Lake or SQL endpoint.

3.3 When to Use a Warehouse

Use Warehouse when:

• You want a SQL-first experience.
• Your team is comfortable with T-SQL.
• You need strong transactional semantics and familiar relational patterns.

You can still land raw data in a Lakehouse and push curated tables into a Warehouse.

4. Migration Strategy: Incremental, Not Big Bang

A practical migration has three phases:

1. Coexistence – Keep existing dataflows; start building Fabric equivalents.
2. Parallel Run – Run both paths, compare outputs.
3. Cutover – Switch datasets to the Fabric sources.

4.1 Coexistence: Build the Fabric Twin

For each target dataflow:

1. Create a Fabric workspace mapped to your capacity.
2. Set up Lakehouse or Warehouse as your storage target.
3. Create Dataflow (Gen2) or pipeline that replicates the logic.

Example: migrating a dataflow that loads a SQL table and filters the last 24 months.

Power BI Dataflow (Power Query)

let
    Source = Sql.Database("SERVER", "SalesDB"),
    dbo_FactSales = Source{[Schema="dbo",Item="FactSales"]}[Data],
    ChangedType = Table.TransformColumnTypes(
        dbo_FactSales,
        {{"OrderDate", type date}, {"SalesAmount", type number}}
    ),
    FilteredRows = Table.SelectRows(
        ChangedType,
        each [OrderDate] >= Date.AddMonths(Date.From(DateTime.LocalNow()), -24)
    )
in
    FilteredRows

In Fabric Dataflows (Gen2), you can reuse almost the same M code. The main differences are:

• Connection configuration (Fabric-managed vs gateway).
• Output destination (Lakehouse table instead of dataflow entity).

4.2 Parallel Run: Validate Outputs

Once the Fabric twin is built:

• Schedule both the classic dataflow and Fabric process.
• Compare row counts and key metrics.
• Use simple validation queries from a Lakehouse SQL endpoint.

Example validation query:

SELECT
    COUNT(*) AS RowCount,
    SUM(SalesAmount) AS TotalSales
FROM
    lakehouse.sales.FactSales
WHERE
    OrderDate >= DATEADD(MONTH, -24, CAST(GETDATE() AS date));

Cross-check these against your existing dataset measures.

4.3 Cutover: Switch Power BI Datasets

When you’re confident in the Fabric outputs:

1. Duplicate the dataset (so you have a rollback option).
2. Replace dataflow connections with Fabric tables.
3. Rebind reports to the new dataset.

To minimize risk:

• Switch low-risk datasets first.
• Run smoke tests: refresh, key visuals, RLS.
• Monitor refresh times and capacity usage after cutover.

5. Reusing Power Query Logic Safely

The biggest time saver is reusing your existing Power Query code. But a straight copy-paste can hide problems.

5.1 Common Adjustments When Moving M to Fabric

Watch out for:

• Custom functions

  • Ensure any custom functions are included or refactored.

• Gateway dependencies

  • On-prem sources still need gateways; verify the Fabric workspace is wired to the same gateway.

• Relative paths / environment-specific values

  • Replace hard-coded server names or file paths with parameters.

Example of parameterizing a server name:

let
    ServerName = #"ServerNameParameter",
    Source = Sql.Database(ServerName, "SalesDB"),
    dbo_FactSales = Source{[Schema="dbo",Item="FactSales"]}[Data]
in
    dbo_FactSales

5.2 Performance Tweaks

Fabric will happily run your old M code, but you can improve performance by:

• Pushing filters as early as possible.
• Avoiding unnecessary Table.Buffer calls.
• Reducing nested joins; consider staging tables in Lakehouse and joining via SQL.

6. Designing a Fabric-Friendly Data Architecture

Migrating dataflows is a chance to clean up your architecture.

6.1 Separate Raw, Curated, and Application Layers

Use a simple three-layer pattern in Fabric:

1. Raw layer

   • Land data as-is from source systems.
   • Typically in a Lakehouse raw schema.

2. Curated layer

   • Apply business rules, conform dimensions, standardize columns.
   • In Lakehouse curated schema or a Warehouse.

3. Application layer

   • Data marts or star schemas optimized for reporting.
   • Consumed by Power BI via Direct Lake or Import.

Example naming convention in a Lakehouse:

• raw.Sales_ERP – direct extract from ERP.
• curated.DimCustomer – cleaned and conformed.
• mart.FactSales – star schema fact table.

6.2 Use Domains and Workspaces Intentionally

Organize Fabric workspaces around domains or products, not just technology:

• Sales Analytics
• Finance Analytics
• HR Analytics

Within each domain workspace:

• One or more Lakehouses.
• Dataflows (Gen2) for ingestion/transformation.
• Pipelines for orchestration.
• Power BI semantic models.

This keeps ownership clear and reduces cross-workspace dependencies.

7. Orchestrating Refresh: From “Refresh Now” to Pipelines

Classic dataflows often rely on scheduled refresh with little orchestration. Fabric gives you proper pipelines.

7.1 Basic Pipeline Pattern

A simple pipeline replacing multiple dataflows:

1. Activity 1 – Ingest raw data via Dataflow (Gen2) into raw tables.
2. Activity 2 – Transform into curated tables using notebooks or dataflows.
3. Activity 3 – Populate mart tables.
4. Activity 4 – Refresh Power BI semantic models.

You can use SQL for some transformations:

INSERT INTO mart.FactSales
SELECT
    s.SalesOrderID,
    s.OrderDate,
    c.CustomerKey,
    p.ProductKey,
    s.SalesAmount
FROM
    curated.Sales s
    JOIN curated.DimCustomer c ON s.CustomerID = c.CustomerID
    JOIN curated.DimProduct p ON s.ProductID = p.ProductID;

7.2 Handling Dependencies

Instead of chaining dataflows via scheduled refresh times, use pipeline activities with:

• On-success dependencies.
• Retry policies.
• Failure notifications.

This gives you deterministic refresh sequences and easier troubleshooting.

8. Security, RLS, and Governance Considerations

Migrating to Fabric changes where you enforce security.

8.1 Where to Apply Security

• Storage level

  • Use workspace permissions and item-level access.

• Semantic model level

  • Keep Row-Level Security (RLS) in Power BI models.

• Data-level masking

  • Apply masking or hashing in curated layer when needed.

Example RLS filter in Power BI model (DAX):

[Region] = LOOKUPVALUE(
    'UserRegion'[Region],
    'UserRegion'[UPN],
    USERPRINCIPALNAME()
)

8.2 Governance Practices During Migration

• Tag Fabric items (Lakehouses, dataflows, models) with metadata:

  • Owner
  • Domain
  • Criticality (e.g., Bronze/Silver/Gold)

• Keep a simple catalog in a shared location.

• Establish a naming convention and stick to it from day one.

9. A Practical Takeaway: Start with One Shared Dataflow

You don’t need a grand migration program to start. Pick a single, heavily reused Power BI dataflow (for example, your core sales dataflow), and:

1. Rebuild it as a Dataflow (Gen2) landing in a Lakehouse.
2. Run it in parallel for a week.
3. Switch one non-critical dataset to the Fabric version.

Once that pattern works, you can repeat it with confidence. Migration from Power BI dataflows to Microsoft Fabric isn’t about rewriting everything; it’s about moving your most valuable transformations into a platform that’s easier to scale, govern, and reuse.