From Excel Power Query to Power BI Dataflows: A Practical Migration Playbook

Why Move from Excel Power Query to Power BI Dataflows?

Most teams move to dataflows because they hit one or more of these pain points:

• Same query everywhere – every analyst has a copy of the same Power Query logic in their workbook.
• Inconsistent logic – someone tweaks a filter in one workbook and numbers stop matching.
• Manual refreshes – scheduled refresh in Power BI, but Excel-based ETL still relies on people.
• Scaling limits – Excel starts choking on 500k+ rows, or file size becomes unmanageable.

Power BI Dataflows help by:

• Centralising transformation logic.
• Enabling scheduled refresh in the service.
• Feeding multiple datasets and reports from the same curated entities.
• Providing a stepping stone toward more formal data engineering without jumping straight to full-blown data warehouses.

The rest of this article walks through a practical migration playbook that you can apply to an existing Excel-based solution.

Step 1: Take Inventory of Your Excel Power Queries

Before you touch Power BI, you need to know what you’re migrating.

1.1 Identify Candidate Workbooks

Start with:

• Core reports used by many users (e.g. monthly sales, financials).
• Workbooks with multiple Power Query queries feeding many pivot tables.
• Files used as data sources for multiple other workbooks.

Avoid migrating everything at once. Pick one business process (e.g. sales reporting) and complete that journey end-to-end.

1.2 Document the Current ETL

For each workbook, capture:

• Data sources
  • Files (CSV, Excel, JSON)
  • Databases (SQL Server, Oracle, etc.)
  • APIs (SharePoint, web services)
• Transformation steps
  • Filters, merges, groupings
  • Calculated columns
  • Custom functions
• Outputs
  • Which queries feed pivot tables / Power Pivot models
  • Which queries are only intermediate staging

A quick way to document:

1. Open Power Query Editor in Excel.
2. For each query:
   • Note the source step.
   • Note any merge/join steps.
   • Note group by or custom column steps.

You don’t need a novel, just enough to map Excel queries to future dataflow entities.

Step 2: Design the Shared Data Model Before You Migrate

Moving queries as-is into dataflows usually recreates the same mess in the cloud. Use the migration to standardise.

2.1 Separate Staging and Model Layers

Think in layers:

• Staging – raw-ish data from source systems, lightly cleaned (types, basic filters).
• Model – business-ready tables with dimensions and facts.

In dataflows, this typically means:

• One dataflow for staging (e.g. DF_Staging_Sales).
• One or more dataflows for curated entities (e.g. DF_Model_Sales), referencing staging entities.

2.2 Define Shared Dimensions and Facts

Look across your Excel queries and identify:

• Dimensions – Date, Customer, Product, Region.
• Fact tables – Sales, Inventory, Orders, Invoices.

Example mapping:

• Multiple Excel queries Sales_2023, Sales_2024 → single FactSales entity.
• Different customer lists in various files → one DimCustomer entity.

Aim for:

• Conformed dimensions – same Customer ID across reports.
• Minimal duplication – each fact/dimension built once, reused.

Step 3: Convert Excel Power Query (M) to Dataflows

The good news: Power BI Dataflows use the same M language as Excel Power Query. Most of your logic can be copy-pasted with minor tweaks.

3.1 Create a New Dataflow

In the Power BI Service:

1. Go to your Workspace.
2. Click New > Dataflow (Gen1 or Gen2 depending on your tenant).
3. Choose Add new tables.
4. Select the data source type matching your Excel query.

3.2 Copy M Code from Excel

In Excel:

1. Open Power Query Editor.
2. Select a query.
3. Go to View > Advanced Editor.
4. Copy the M code.

In Dataflows:

1. In the dataflow editor, connect to the same source.
2. After the initial connection, open Advanced Editor.
3. Replace the generated M with your copied M.

You may need to adjust:

• File paths (e.g. from local C:\ to SharePoint/OneDrive URLs).
• Credentials and privacy levels.
• Any references to Excel-specific functions.

3.3 Example: Migrating a Simple Sales Query

Original Excel Power Query (simplified):

let
    Source = Excel.Workbook(File.Contents("C:\Data\Sales.xlsx"), null, true),
    Sales_Sheet = Source{[Name="Sales"]}[Content],
    ChangedTypes = Table.TransformColumnTypes(Sales_Sheet,
        {{"Date", type date}, {"CustomerID", Int64.Type}, {"Amount", type number}}),
    FilteredRows = Table.SelectRows(ChangedTypes, each [Amount] > 0)
in
    FilteredRows

Dataflow version pointing to SharePoint:

let
    Source = Excel.Workbook(
        Web.Contents("https://contoso.sharepoint.com/sites/BI/Shared%20Documents/Sales.xlsx"),
        null,
        true
    ),
    Sales_Sheet = Source{[Name="Sales"]}[Content],
    ChangedTypes = Table.TransformColumnTypes(Sales_Sheet,
        {{"Date", type date}, {"CustomerID", Int64.Type}, {"Amount", type number}}),
    FilteredRows = Table.SelectRows(ChangedTypes, each [Amount] > 0)
in
    FilteredRows

The transformation steps stay identical; only the data source changes.

Step 4: Build Reusable Entities and Reference Logic

Dataflows become powerful when entities reference each other, not when every entity starts from the source.

4.1 Create Staging Entities

For each source system:

• Create a staging entity that:
  • Reads from the source.
  • Fixes data types.
  • Applies minimal filters (e.g. remove null keys).

Example staging entity for a SQL table:

let
    Source = Sql.Database("sql-prod", "SalesDW"),
    SalesTable = Source{[Schema="dbo",Item="Sales"]}[Data],
    ChangedTypes = Table.TransformColumnTypes(SalesTable,
        {{"SalesDate", type date}, {"CustomerID", Int64.Type}, {"NetAmount", type number}})
in
    ChangedTypes

4.2 Reference Staging for Dimensions and Facts

Instead of reloading from the source, reference the staging entity:

let
    Source = #"Stg_Sales", // staging entity
    FilteredRows = Table.SelectRows(Source, each [NetAmount] > 0),
    AddedYear = Table.AddColumn(FilteredRows, "Year", each Date.Year([SalesDate]), Int64.Type)
in
    AddedYear

This pattern:

• Keeps source access logic in one place.
• Allows multiple entities to reuse the same staging data.
• Simplifies future changes (e.g. new columns, renamed fields).

Step 5: Wire Dataflows into Power BI Datasets

Once you’ve built your entities, you need to hook them up to Power BI reports.

5.1 Connect a Dataset to a Dataflow

In Power BI Desktop:

1. Click Get data > Power Platform > Power BI dataflows.
2. Sign in and choose the workspace.
3. Select the entities you created (e.g. FactSales, DimCustomer).
4. Load them into the model.

Then:

• Create relationships (e.g. DimCustomer[CustomerID] → FactSales[CustomerID]).
• Add measures in DAX.

Example measure:

Total Sales = SUM ( FactSales[NetAmount] )

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

5.2 Avoid Logic Duplication in the Dataset

Keep the dataset lean:

• Do data shaping (types, splits, merges, filters) in dataflows.
• Do business calculations (measures, KPIs) in the dataset.

If you find yourself adding many calculated columns in Power BI that could be done upstream, consider pushing them back into the dataflow.

Step 6: Handle Incremental Refresh and Performance

With dataflows, you can offload heavy ETL from Power BI Desktop and improve refresh performance.

6.1 Use Incremental Refresh Where Possible

For large fact tables:

• Configure incremental refresh either:
  • In the dataset (most common), or
  • In dataflows (where supported in your environment).

Basic pattern:

1. Ensure you have a date column suitable for partitioning (e.g. SalesDate).
2. Define parameters (e.g. RangeStart, RangeEnd) and use them in your dataflow query.

Example parameterised filter in M:

let
    Source = #"Stg_Sales",
    FilteredRows = Table.SelectRows(Source,
        each [SalesDate] >= RangeStart and [SalesDate] < RangeEnd)

in
    FilteredRows

3. Configure incremental refresh in the dataset using the same parameters.

6.2 Monitor Refresh Failures Early

Common failure causes after migration:

• Changed file paths or SharePoint URLs.
• Credentials not set correctly in the service.
• Gateway misconfiguration for on-prem sources.

Set up:

• Email notifications for refresh failures.
• A simple log table/entity (e.g. ETL_Log) if you want to track row counts or timestamps.

Step 7: Governance: Naming, Versioning, and Ownership

Dataflows are shared assets. Without discipline, they become another swamp.

7.1 Naming Conventions

Adopt a consistent pattern, for example:

• Dataflows:
  • DF_Stg_<Domain> – e.g. DF_Stg_Sales
  • DF_Model_<Domain> – e.g. DF_Model_Sales
• Entities:
  • Stg_<Table> – e.g. Stg_SalesRaw
  • Dim_<Name> – e.g. DimCustomer
  • Fact_<Name> – e.g. FactSales

7.2 Ownership and Change Control

Define:

• Owner per dataflow – responsible for changes and quality.
• Change process – even if lightweight:
  • Log changes (what, when, why).
  • Communicate breaking changes (e.g. column renames) to report owners.

Consider using a simple change log in a shared document or a ticketing system.

Common Migration Pitfalls (and How to Avoid Them)

Pitfall 1: Rebuilding Everything in One Go

• Problem: Big-bang migration leads to half-finished dataflows and broken reports.
• Fix: Migrate one business process at a time and fully decommission the old Excel logic before moving on.

Pitfall 2: Keeping Local Excel Files as Sources

• Problem: Dataflows can’t access someone’s C:\ drive; refresh fails in the service.
• Fix: Move source files to SharePoint/OneDrive or a central file share behind a gateway.

Pitfall 3: Overloading Dataflows with Business Logic

• Problem: Every new calculation gets added to the dataflow, making it hard to maintain.
• Fix: Keep dataflows focused on clean, conformed data; use DAX for report-specific measures.

Pitfall 4: Ignoring Security

• Problem: Sensitive data ends up in a shared dataflow with broad access.
• Fix:
  • Use separate workspaces for sensitive domains.
  • Limit who can edit dataflows.
  • Use row-level security in datasets where needed.

A Practical Next Step

Pick one high-impact Excel report, identify its core Power Query logic, and move just that into a Power BI dataflow with a clear staging and model layer. Wire a single dataset to that dataflow, verify numbers with your users, and only then expand to other reports—this small, end-to-end migration will give you a repeatable pattern for the rest of your shared data models.