From Excel to Microsoft Fabric: A Practical Migration Path for Workbook‑Heavy Analysts

1. Start With the Right Mindset: Don’t “Lift and Shift” Your Workbook

The biggest mistake is trying to recreate your workbook exactly as it is, just in the cloud. Fabric is not “Excel Online Pro” — it’s a different shape of solution.

Think in terms of layers:

1. Data layer – where raw data lives (in Fabric: Lakehouse, Warehouse).
2. Transform layer – where you clean and reshape data (in Fabric: Dataflows Gen2, notebooks, pipelines).
3. Model layer – where relationships, measures, and business logic sit (in Fabric: semantic models / Power BI datasets).
4. Presentation layer – where users consume (in Fabric: Power BI reports, Excel connected to models).

In Excel, these are usually all jammed into one file. Your migration path is basically: tease these layers apart without disrupting your users.

2. Inventory Your Existing Excel Estate

Before you touch Fabric, map what you actually have. You don’t need a formal audit, but you do need a clear picture.

2.1 Classify Workbooks by Role

Create a simple list of critical files and label each one:

• Data collectors
  • Manual inputs, forms, data entry templates.
• Data dumps
  • Exports from ERP/CRM, CSV imports, flat tables.
• Transformation workbooks
  • Heavy use of Power Query, complex formulas, VBA clean‑ups.
• Reporting models
  • Pivot tables, cube formulas, charts, dashboards.

You’ll migrate these roles differently.

2.2 Identify What Must Not Break

For each key workbook, note:

• Who uses it.
• How often (daily/weekly/monthly).
• Which outputs are “sacred”: specific sheets, tables, or charts that must keep working.

These “sacred outputs” will guide where you keep Excel in the loop versus where you move to Power BI.

3. Step 1: Land Your Excel Data in a Fabric Lakehouse

First goal: stop treating Excel files as the system of record. Make Fabric the source of truth, Excel the client.

3.1 Organise Your Files in OneLake

Typical path:

1. Create a Workspace in Fabric for your team.
2. Create a Lakehouse (e.g., SalesAnalytics_LH).
3. In the Lakehouse, define folders like:
   • /raw/erp_exports/
   • /raw/crm_exports/
   • /raw/manual_inputs/

Upload existing Excel/CSV files into raw. For recurring files, plan to automate ingestion (next section).

3.2 Automate Ingestion With Data Pipelines

Use a Data Pipeline (or Dataflow Gen2 if you prefer Power Query) to pick up Excel files and land them into delta tables.

High‑level pattern:

1. Copy activity from your source (SharePoint/OneDrive/file share) into the Lakehouse Files area.
2. Table creation using a notebook or Dataflow Gen2 to convert files into tables.

Example: simple PySpark notebook to convert an Excel file to a delta table:

from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()

# Read Excel from OneLake Files
input_path = "abfss://workspace@onelake.dfs.fabric.microsoft.com/lakehouse/Files/raw/erp_exports/sales.xlsx"

df = spark.read.format("com.crealytics.spark.excel").options(
    header="true",
    inferSchema="true"
).load(input_path)

# Write as delta table in Lakehouse
output_path = "Tables/sales_raw"

df.write.mode("overwrite").format("delta").save(output_path)

Schedule this notebook in a pipeline to keep your sales_raw table up to date.

3.3 Quick Win: Use Dataflows Gen2 for Excel‑Style Transformations

If you’re already comfortable with Power Query in Excel, Dataflows Gen2 is the easiest on‑ramp.

Typical steps:

1. Create a Dataflow Gen2.
2. Use Get data > Excel/CSV from OneLake or SharePoint.
3. Apply your familiar transformations in Power Query.
4. Output to Lakehouse table.

Sample Power Query (M) to clean an Excel export:

let
    Source = Excel.Workbook(File.Contents("sales.xlsx"), true),
    Sales_Sheet = Source{[Item="Sales",Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(Sales_Sheet, [PromoteAllScalars=true]),
    ChangedTypes = Table.TransformColumnTypes(PromotedHeaders,{
        {"OrderDate", type date},
        {"Amount", type number},
        {"Region", type text}
    }),
    FilteredRows = Table.SelectRows(ChangedTypes, each [Amount] > 0)
in
    FilteredRows

4. Step 2: Move Transformations Out of Excel

Once your raw data is in the Lakehouse, the next step is to relocate transformation logic.

4.1 Migrate Power Query from Excel to Dataflows Gen2

For workbooks that already use Power Query:

1. Open Power Query in Excel.
2. Copy the M code of each query.
3. Paste into a Dataflow Gen2 query.
4. Point the source step to the file/table in OneLake instead of the local file.

This keeps your logic almost identical, but runs it centrally.

4.2 Replace Formula‑Based Transformations

Look for heavy formula sheets used purely to reshape data (e.g., OFFSET, INDEX/MATCH, TEXT, LEFT/RIGHT, SUMIFS chains). These usually become either:

• Power Query steps (preferred).
• SQL in a Warehouse for more advanced joins or aggregations.

Example: a SUMIFS‑based aggregation in Excel:

'Simplified example in Excel
=SUMIFS(Sales[Amount], Sales[Region], "West", Sales[Year], 2024)

Equivalent DAX measure in a Fabric semantic model:

Sales West 2024 = 
CALCULATE(
    SUM(Sales[Amount]),
    Sales[Region] = "West",
    Sales[Year] = 2024
)

The key move: aggregation logic lives in DAX, not scattered SUMIFS across sheets.

5. Step 3: Build a Fabric Semantic Model That Feels Like a Clean Excel Model

With clean tables in your Lakehouse/Warehouse, create a semantic model (dataset) that mirrors how you wish your Excel model looked.

5.1 Design a Star Schema

From your tables, aim for:

• Fact tables
  • FactSales, FactBudget, FactInventory.
• Dimension tables
  • DimDate, DimCustomer, DimProduct, DimRegion.

In Fabric (via Power BI experience):

1. Create a semantic model from Lakehouse/Warehouse tables.
2. Define relationships (one‑to‑many) between dimensions and facts.
3. Hide technical columns; keep the field list clean for users.

5.2 Rebuild Key Excel Calculations as DAX Measures

Start with the measures that drive your reports today.

Examples:

Total Sales = SUM(FactSales[Amount])

Total Budget = SUM(FactBudget[BudgetAmount])

Sales vs Budget % = 
DIVIDE(
    [Total Sales] - [Total Budget],
    [Total Budget]
)

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

Keep measure names business‑friendly. These will show up both in Power BI and in Excel pivot tables connected to the model.

6. Step 4: Keep Excel as a Front‑End (At Least for Now)

You don’t have to push everyone into Power BI on day one. Excel can sit on top of Fabric models quite comfortably.

6.1 Connect Excel to Your Fabric Semantic Model

In Excel:

1. Data > Get Data > From Power BI (or From Database > From Power BI dataset).
2. Choose your Fabric semantic model.
3. Insert a PivotTable.

Now your users can:

• Drag fields just like they do with local tables.
• Refresh directly from Fabric.
• Use the same measures as Power BI.

This is a big psychological win: data is centralised, but Excel still feels familiar.

6.2 Replace Hidden Calculation Sheets With Model‑Driven Logic

Where you previously had hidden sheets with intermediate calculations:

• Move those calculations into DAX measures.
• Use cube formulas or pivot tables to reference measures in your existing layout.

Example cube formula in Excel:

= CUBEVALUE(
    "ThisWorkbookDataModel",
    "[Measures].[Sales vs Budget %]",
    SLICER_MEMBER("DimRegion[Region]", "West"),
    SLICER_MEMBER("DimDate[Year]", 2024)
)

This lets you keep a highly formatted Excel dashboard while the logic lives in Fabric.

7. Step 5: Gradually Shift Presentation to Power BI

Once the data and logic are centralised, moving from Excel dashboards to Power BI is mostly a UX decision.

7.1 Identify Reports That Benefit Most From Power BI

Prioritise:

• Reports with many manual refresh steps.
• Workbooks frequently emailed around as attachments.
• Dashboards where users constantly ask for interactivity or mobile access.

For each candidate, rebuild the key visuals in a Power BI report using your existing semantic model.

7.2 Use Power BI for Navigation, Excel for Detail

A pragmatic pattern:

• Power BI for high‑level dashboards, KPIs, filters.
• Excel for detailed analysis, ad‑hoc pivots, and export‑heavy users.

You can:

• Link from a Power BI visual to an Excel template connected to the same model.
• Or provide an “Export to Excel” path for specific detail tables.

8. Governance, Performance, and Scheduling: Fabric vs. Workbook World

Once your first migrated flows are live, stabilise them with a few key practices.

8.1 Replace Manual Refresh Rituals With Scheduled Pipelines

Instead of:

• “Every Monday, open SalesReport.xlsx, refresh all, save, email.”

Do:

1. Schedule Dataflows Gen2 or pipelines to refresh staging and curated tables.
2. Schedule semantic model refreshes after data is loaded.
3. Notify users only if something fails.

8.2 Version Control and Change Management

Basic rules that work well in Fabric:

• Use separate workspaces for Dev / Test / Prod.
• Keep notebooks, M code, and DAX in source control (Git integration or even a simple repo).
• Promote only tested pipelines and models into Prod.

8.3 Performance Tips for Ex‑Excel Models

If you’re used to fighting slow workbooks, the same ideas apply:

• Avoid row‑by‑row logic in DAX; push heavy transformations to Power Query or SQL.
• Prefer star schemas over snowflakes or flat “one giant table” designs.
• Use aggregations or summary tables for very large fact tables.

9. A Concrete, Next‑Week Action Plan

To make this real, here’s a simple 4‑week plan you can actually follow:

Week 1

• Pick one important workbook.
• Classify its role and identify sacred outputs.
• Land its raw data into a Fabric Lakehouse.

Week 2

• Move its Power Query / transformation logic into a Dataflow Gen2.
• Create clean fact/dimension tables.

Week 3

• Build a semantic model with key measures.
• Connect Excel to that model and recreate the main pivot tables.

Week 4

• Create a basic Power BI report on the same model.
• Show both the Excel and Power BI views to your users and agree on next candidates.

If you do just this for one workbook, you’ll have a repeatable pattern for the rest of your Excel estate: Fabric as the source of truth, transformations centralised, models shared, and Excel demoted from “database” to a flexible front‑end.

One Practical Takeaway

Don’t try to migrate all your workbooks at once. Choose a single, high‑value Excel report, move its data and transformations into Fabric, connect Excel back to the new semantic model, and only then start rebuilding the visuals in Power BI — that sequence keeps users productive while you quietly modernise everything underneath.