From Excel Power Query to Microsoft Fabric Dataflows: A Practical Migration Guide for Analysts

1. Why move from Excel Power Query to Fabric dataflows?

You don’t move to Fabric dataflows just because it’s new; you move because your current Excel setup is hitting limits.

Typical signs it’s time to migrate:

• Workbooks over a few dozen MB that refresh slowly or crash.
• Multiple analysts copy-pasting the same Power Query logic into different files.
• Data sources that should be centralized (ERP, CRM, data warehouse) but are being pulled directly into Excel.
• Need for scheduled refreshes and access control beyond what OneDrive/SharePoint can give you.

Fabric dataflows help by:

• Centralizing your M logic in one place.
• Writing outputs to Lakehouse tables or Power BI datasets instead of embedding data in workbooks.
• Allowing scheduled refreshes and better governance.
• Letting Power BI and other Fabric items reuse the same transformations.

If you already know Power Query, the migration is more about architecture and patterns than learning a new language.

2. Key conceptual differences: Excel vs Fabric dataflows

Before you migrate, align on what changes when you leave Excel.

2.1 Where the data lives

• Excel Power Query

  • Data is loaded into the workbook (tables, data model) or stays as connection-only.
  • Versioning and access are file-based.

• Fabric dataflows

  • Data is written to:
    • Lakehouse tables (Delta), or
    • Power BI semantic models (if using dataflows in Power BI workspace).
  • Access is controlled at workspace/item level.

2.2 Execution environment

• Excel refresh runs on the user’s machine.
• Fabric refresh runs in the service, using gateways for on-prem data.

This means:

• No more “it works on my laptop but not on the server” excuses.
• You must think about credentials and gateways upfront.

2.3 Authoring experience

• Excel: Power Query Editor inside the workbook.
• Fabric: Dataflow authoring in the browser (Power Query Online), or via tools like Power BI Desktop and then publishing.

Most M functions are the same, but there are differences around:

• File system paths.
• Local Excel-only functions.
• Some UI-only features.

3. Migration checklist: what to review before moving

Don’t just copy M code and hope for the best. Use this checklist on your existing Excel queries.

3.1 Inventory your queries

For each workbook:

1. List all queries in the Power Query editor.
2. Mark which ones:
   • Load to sheet.
   • Load to data model.
   • Are connection-only.
3. Identify dependencies (queries referencing other queries).

3.2 Classify by purpose

Group queries into:

• Staging: Raw imports, minimal transformations.
• Business logic: Joins, aggregations, business rules.
• Presentation: Final shaping for a specific report or pivot.

In Fabric, staging and business logic are good candidates for shared dataflows. Presentation logic often stays closer to the report (Power BI model).

3.3 Check for Excel-specific dependencies

Look for:

• Excel.CurrentWorkbook() or Excel.Workbook(File.Contents(...)) pointing to local files.
• Functions that rely on named ranges or current sheet.
• Hard-coded file paths like C:\Users\....

These need to be replaced with:

• SharePoint/OneDrive URLs.
• Fabric Lakehouse paths.
• Other cloud-accessible sources.

4. Migrating a simple Excel Power Query to Fabric step-by-step

Let’s walk through a concrete example: an Excel Power Query that imports CSV sales data from a local folder, cleans it, and loads it into a table.

4.1 Original Excel Power Query (M code)

let
    Source = Folder.Files("C:\Data\Sales"),
    Filtered = Table.SelectRows(Source, each [Extension] = ".csv"),
    AddedContent = Table.AddColumn(Filtered, "Data", each Csv.Document(File.Contents([Folder Path] & [Name]),
        [Delimiter = ",", Columns = 6, Encoding = 65001, QuoteStyle = QuoteStyle.Csv])),
    Expanded = Table.ExpandTableColumn(AddedContent, "Data", {"Date","Region","Product","Qty","Price","Salesperson"}),
    ChangedTypes = Table.TransformColumnTypes(Expanded,
        {{"Date", type date}, {"Qty", Int64.Type}, {"Price", type number}}),
    AddedAmount = Table.AddColumn(ChangedTypes, "Amount", each [Qty] * [Price], type number)
in
    AddedAmount

4.2 Target architecture in Fabric

We’ll:

1. Store the CSVs in a Fabric-connected location (e.g., SharePoint or a Lakehouse Files area).
2. Create a dataflow that:
   • Reads from that location.
   • Cleans and transforms the data.
   • Outputs to a Lakehouse table Sales.CleanedSales.
3. Use that table in Power BI and other Fabric items.

4.3 Adjusting the M code for Fabric

Assume you upload your CSVs to a SharePoint document library.

let
    Source = SharePoint.Files("https://contoso.sharepoint.com/sites/Sales", [ApiVersion = 15]),
    FilteredFolder = Table.SelectRows(Source, each Text.StartsWith([Folder Path], "/sites/Sales/Shared Documents/Sales/")),
    FilteredCsv = Table.SelectRows(FilteredFolder, each [Extension] = ".csv"),
    AddedContent = Table.AddColumn(FilteredCsv, "Data", each Csv.Document([Content],
        [Delimiter = ",", Columns = 6, Encoding = 65001, QuoteStyle = QuoteStyle.Csv])),
    Expanded = Table.ExpandTableColumn(AddedContent, "Data", {"Date","Region","Product","Qty","Price","Salesperson"}),
    ChangedTypes = Table.TransformColumnTypes(Expanded,
        {{"Date", type date}, {"Qty", Int64.Type}, {"Price", type number}}),
    AddedAmount = Table.AddColumn(ChangedTypes, "Amount", each [Qty] * [Price], type number)
in
    AddedAmount

In Fabric dataflows, you’ll define the output entity to write to a Lakehouse table. The M step is similar; the main difference is the data source and where the result lands.

5. Building your first Fabric dataflow from Excel queries

5.1 Exporting M code from Excel

To reuse existing logic:

1. Open your Excel workbook.
2. Go to Data > Queries & Connections.
3. Right-click a query > Edit.
4. In Power Query Editor, go to View > Advanced Editor.
5. Copy the M code.

5.2 Creating the dataflow in Fabric

In your Fabric workspace:

1. Click New > Dataflow Gen2 (or equivalent in your environment).
2. Choose Blank query.
3. Open the Advanced Editor.
4. Paste your M code.
5. Fix any local paths and Excel-specific functions.
6. Validate the query and preview data.
7. Define the output:
   • Choose the Lakehouse and table name.
   • Confirm column types.

5.3 Scheduling refreshes

Once the dataflow works:

1. Open the dataflow settings.
2. Configure refresh schedule:
   • Frequency (daily, hourly, etc.).
   • Time zone.
3. Ensure the data gateway is configured for on-prem sources.

Now your former Excel-only transformation runs centrally, on a schedule, feeding a Lakehouse table.

6. Common M code adjustments when moving to Fabric

Most Excel Power Query scripts work in Fabric with minor tweaks. Here are the patterns you’ll hit most often.

6.1 Replacing Excel.CurrentWorkbook

Excel-only:

let
    Source = Excel.CurrentWorkbook(){[Name="Config"]}[Content]
in
    Source

In Fabric, store configuration in a table or file instead. For example, a CSV in SharePoint:

let
    Source = SharePoint.Files("https://contoso.sharepoint.com/sites/Config", [ApiVersion = 15]),
    ConfigFile = Source{[Name="Config.csv"]}[Content],
    ConfigTable = Csv.Document(ConfigFile, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv])
in
    ConfigTable

6.2 Local file paths to cloud paths

Replace:

File.Contents("C:\Data\Customers.xlsx")

With something like:

SharePoint.Files("https://contoso.sharepoint.com/sites/Data", [ApiVersion = 15])

And then filter to the file you need:

let
    Source = SharePoint.Files("https://contoso.sharepoint.com/sites/Data", [ApiVersion = 15]),
    CustomersFile = Source{[Name="Customers.xlsx"]}[Content],
    Customers = Excel.Workbook(CustomersFile, null, true)
in
    Customers

6.3 Avoiding UI-only features

In Excel, some transformations are created via UI steps that rely on workbook context. In Fabric, prefer explicit M functions:

• Use Table.TransformColumns instead of relying on implicit type detection.
• Use Table.Buffer carefully to control query folding (only when needed).

Example of explicit type conversion (good for Fabric):

Table.TransformColumnTypes(MyTable,
    {{"OrderDate", type date}, {"Amount", type number}})

7. Designing shared dataflows for multiple reports

Once you migrate a few queries, you’ll quickly see repeated patterns. Use Fabric dataflows as shared building blocks.

7.1 Split by domain, not by report

Instead of one giant dataflow per report, organize by data domain:

• DF_Sales_Staging
• DF_Sales_BusinessLogic
• DF_Finance_Staging
• DF_MasterData_Customers

Benefits:

• Multiple reports reuse the same curated tables.
• Changes to business logic happen in one place.

7.2 Layering pattern

A simple layering approach:

1. Staging dataflows

   • Raw ingestion from sources.
   • Minimal transformations (type casting, basic cleanup).

2. Business dataflows

   • Join staging entities.
   • Apply business rules, mappings, and calculated columns.

3. Semantic models (Power BI)

   • Measures, hierarchies, and report-specific logic.

This mirrors what many analysts already do in Excel (raw sheets, cleaned sheets, pivot sheets), but with Fabric-native components.

8. Handling incremental refresh and large datasets

Fabric dataflows are more comfortable with larger datasets than Excel, but you still need to design for refresh performance.

8.1 Partitioning via date filters

If your source supports query folding, you can implement incremental logic in the dataflow by filtering on date columns.

Example M pattern for incremental behavior:

let
    // Assume RangeStart and RangeEnd are parameters
    Source = Sql.Database("server", "db"),
    Sales = Source{[Schema="dbo",Item="Sales"]}[Data],
    Filtered = Table.SelectRows(Sales, each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd)
in
    Filtered

Even if you don’t configure full-blown incremental refresh policies, this pattern lets you control how much data you process per run.

8.2 Avoid heavy transformations early

General rule:

• Push filters and joins as close to the source as possible.
• Avoid row-by-row custom functions on large tables.

If you must do heavy logic, consider:

• Splitting into multiple dataflows.
• Using Lakehouse notebooks (Python/SQL) for very heavy transformations, then using dataflows for lighter shaping.

9. Practical takeaway: start with one high-impact workbook

You don’t need a big-bang migration. Pick one Excel workbook that:

• Has complex Power Query logic.
• Is used by multiple people.
• Refreshes frequently.

Then:

1. Export its M queries.
2. Create a Fabric dataflow that reproduces the logic.
3. Write the output to a Lakehouse table.
4. Point your Excel or Power BI reports to that shared table instead of the original queries.

Once that one workbook is stable, repeat the pattern. Over time, you’ll move from fragile, file-based Power Query to a reusable Fabric dataflow layer that your entire analytics stack can rely on.