From Excel Power Query to Fabric Dataflows Gen2: A Hands-On Migration Playbook

1. Why Move From Excel Power Query to Dataflows Gen2?

You’re probably here because at least one of these is true:

• Workbooks are getting too big and slow.
• Everyone is copying the same queries into their own files.
• You need refresh schedules, not manual “Refresh All”.
• IT is asking for governance, not random XLSX on SharePoint.

Dataflows Gen2 in Microsoft Fabric give you:

• Centralized M logic – one place to maintain transformations.
• Lake-native storage – data lands in OneLake, ready for Lakehouse, Warehouse, or Power BI.
• Reuse across tools – Power BI, Notebooks, Warehouses can all consume the same curated tables.
• Separation of prep and reporting – analysts focus on models and visuals, not raw data cleanup.

The good news: your Power Query knowledge transfers almost 1:1. The migration work is mostly about rewiring where queries live and where they output.

2. Pre-Migration Checklist: Clean Up Your Excel Queries First

Don’t move a mess. Before touching Fabric, stabilise what you have in Excel.

2.1 Inventory Your Queries

Open your main Excel file and in the Power Query Editor:

• List all queries that load to a sheet.
• List all connection-only queries (used as staging).
• Identify parameters (date ranges, paths, environment flags).
• Note data sources:
  • Excel/CSV files
  • SharePoint/OneDrive
  • SQL Server / other databases
  • Web APIs

Create a simple table like:

This becomes your migration map.

2.2 Simplify and Modularise

You don’t want 200-line monolithic queries in Fabric.

Refactor:

• Separate staging from business logic:
  • f_SalesRaw – just connects and basic type cleanup.
  • Sales_Transform – filters, derived columns, business rules.
• Use functions for repeated logic:
  • Currency conversion
  • Date bucketing (week, quarter, fiscal periods)

Example: turn a repeated date floor into a function.

// In Excel Power Query
let
    fnStartOfWeek = (InputDate as date, OptionalFirstDayOfWeek as nullable number) as date =>
    let
        FirstDay = if OptionalFirstDayOfWeek = null then Day.Monday else OptionalFirstDayOfWeek,
        Result = Date.StartOfWeek(InputDate, FirstDay)
    in
        Result
in
    fnStartOfWeek

You’ll reuse this function in Fabric almost unchanged.

2.3 Remove Excel-Only Dependencies

Dataflows Gen2 don’t know about:

• Excel.CurrentWorkbook()
• Named ranges
• Local file paths on your C: drive

Replace these patterns with:

• External files in OneDrive/SharePoint using SharePoint.Files or SharePoint.Contents.
• Parameters for paths and file names.

Example conversion:

// BEFORE (Excel-only)
Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content]

// AFTER (Fabric-friendly, pointing to SharePoint folder)
Source = SharePoint.Files("https://contoso.sharepoint.com/sites/Finance", [ApiVersion = 15]),
Filtered = Table.SelectRows(Source, each [Folder Path] = "https://contoso.sharepoint.com/sites/Finance/Shared Documents/Data/" and [Name] = "Sales.xlsx"),
File    = Filtered{0}[Content],
Excel   = Excel.Workbook(File, null, true),
Sales   = Excel{[Item="Sales", Kind="Table"]}[Data]

3. Designing Your Target in Fabric: Where Should the Data Land?

Before migrating, decide what the output of your dataflows should be.

Common patterns:

1. Direct to Lakehouse (recommended)

   • Each Dataflow Gen2 writes to a Lakehouse table.
   • Power BI models connect to the Lakehouse.

2. Direct to Warehouse

   • Dataflows populate Warehouse tables.
   • Good when you need strong SQL semantics and stored procedures later.

3. Hybrid

   • Bronze/Silver layers in Lakehouse.
   • Gold layer in Warehouse or a curated Lakehouse schema.

For most Excel migrations, a single Lakehouse with curated tables is a solid starting point.

Decide naming conventions:

• Lakehouse: lh_FinanceAnalytics
• Schemas / folders (if you standardise via shortcuts or views):
  • stg_ for raw/staging
  • dim_ for dimensions
  • fact_ for facts

4. Step-by-Step: Migrating One Excel Query to Dataflows Gen2

Let’s walk through migrating a typical fact table query.

4.1 Create the Dataflow Gen2

In your Fabric workspace:

1. New → Dataflow Gen2.
2. Choose Blank dataflow.
3. In Get data, pick the same source as in Excel (e.g., SQL Server, SharePoint folder).
4. Paste or recreate your connection details.

4.2 Reuse Your M Code

In Excel Power Query:

1. Open the query (e.g., Sales_Transform).
2. Go to Advanced Editor.
3. Copy the entire M script.

In Dataflows Gen2:

1. In Power Query Online, go to Advanced Editor for the new query.
2. Paste the M code.
3. Fix any steps that reference Excel-only functions.

Typical fixes:

• Replace Excel.CurrentWorkbook() with SharePoint or Lakehouse sources.
• Adjust data source paths to Fabric-friendly locations.
• Review privacy levels and credentials.

4.3 Map Output to a Lakehouse Table

Once the query works:

1. In the query’s Settings pane, set Output to Lakehouse.
2. Pick your Lakehouse (lh_FinanceAnalytics).
3. Set table name, e.g. fact_Sales.
4. Configure Incremental refresh if appropriate (we’ll cover that next).

Save and Refresh now to materialise the table.

5. Handling Incremental Refresh and Parameters

Excel usually refreshes all rows every time. Fabric gives you incremental patterns with parameters.

5.1 Converting Date Filters to Parameters

Suppose your Excel query filters to last 2 years:

// Excel logic
FilteredRows = Table.SelectRows(Source, each [OrderDate] >= Date.AddYears(Date.From(DateTime.LocalNow()), -2))

In Fabric, you may want:

• Full history in the table.
• Incremental refresh based on OrderDate.

Steps:

1. Create a parameter RangeStart (type datetime).
2. Create a parameter RangeEnd (type datetime).
3. Adjust the filter step:

FilteredRows = Table.SelectRows(Source, each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd)

Then, in Dataflows Gen2 Incremental refresh settings:

• Use OrderDate as the partition column.
• Set how many periods to store / refresh (e.g., last N days/months).

5.2 Parameterising Environments

If you have different servers for DEV/TEST/PROD, parameterise the connection string.

// Parameter: Environment = "DEV" or "PROD"

ServerName = if Environment = "DEV" then "sql-dev.contoso.local" else "sql-prod.contoso.local",
Source     = Sql.Database(ServerName, "SalesDB")

This makes it easy to clone the dataflow across workspaces.

6. Rebuilding Your Excel Report on Top of Fabric

Once data is in Fabric, you have two main options.

6.1 Rebuild in Power BI (Preferred)

1. In Fabric, create a Power BI semantic model connected to your Lakehouse.
2. Use Model view to:
   • Mark date table.
   • Define relationships between dim_ and fact_ tables.
   • Add calculated columns and measures.

Example measure:

Total Sales := SUM ( fact_Sales[SalesAmount] )

Sales LY :=
CALCULATE (
    [Total Sales],
    DATEADD ( dim_Date[Date], -1, YEAR )
)

YoY % :=
DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] )

3. Build Power BI reports and publish them.
4. For users who still love Excel, connect Excel to the Power BI dataset using Analyze in Excel.

6.2 Keep Excel Front-End, Fabric Back-End

If you must keep Excel as the primary reporting surface:

• Use Get Data → From Power BI to connect to the Fabric semantic model.
• Or connect Excel directly to the Lakehouse via SQL endpoint (Warehouse or Lakehouse SQL view).

The key shift: no more Power Query in the report workbook. Excel becomes a thin client on top of curated Fabric data.

7. Common Pitfalls and How to Avoid Them

7.1 Silent Type Changes

Fabric will happily infer different types if you’re not explicit.

• In your M code, explicitly set types using Table.TransformColumnTypes.

ChangedTypes = Table.TransformColumnTypes(Source,
    {
        {"OrderDate", type date},
        {"OrderID", Int64.Type},
        {"SalesAmount", type number}
    }
)

This keeps your model stable across refreshes.

7.2 Locale Issues (Decimal/Date)

If your Excel files were using a specific locale (e.g., comma decimal separator), Dataflows might interpret them differently.

• Use Culture arguments where available, e.g.:

NumberFromText = Number.FromText("1,23", "ro-RO")

• Normalise early in the pipeline.

7.3 Nested Queries and Load Order

In Excel, load order is often implicit. In Fabric:

• Ensure staging queries (e.g., f_SalesRaw) are not mapped to output tables unless you need them.
• Be careful with query folding: keep filters and joins as high as possible to push work to the source.

7.4 Security Assumptions

Excel often connects with your user credentials. In Fabric:

• Decide between organizational credentials and service principals.
• Align with your tenant’s governance rules.

Don’t assume that “it works on my laptop” will translate directly.

8. A Minimal Migration Roadmap for Teams

If you’re doing this for a department, not just one workbook, use a simple phased plan.

Phase 1 – Pilot

• Pick one high-value Excel report.
• Migrate only the core fact and 1–2 dimensions.
• Rebuild the report in Power BI.
• Validate numbers with business users.

Phase 2 – Standardise

• Define:
  • Naming conventions for Lakehouse tables.
  • Folder/workspace structure.
  • Shared parameter patterns (dates, environments).
• Move repeated logic into M functions and reuse across dataflows.

Phase 3 – Rollout

• Prioritise Excel reports by:
  • Business criticality.
  • Refresh pain.
  • Number of consumers.
• Migrate in waves, keeping old Excel reports as a fallback for a limited time.

Phase 4 – Optimise

• Introduce incremental refresh where data volume justifies it.
• Split huge dataflows into smaller, composable ones (staging vs curated).
• Monitor refresh duration and failures, then tune.

9. Practical Takeaway: Start With One Query, Not the Whole Workbook

Don’t try to “lift and shift” an entire Excel solution in one go. Pick a single, well-understood Power Query (usually your main fact table), move it into a Dataflow Gen2, land it in a Lakehouse table, and point a simple report at it. Once that path is working end to end, cloning the pattern for the rest of your queries becomes a repeatable, low-risk routine instead of a scary migration project.