Power BI to Microsoft Fabric Migration: End-to-End Data Engineering Guide

                       “Data migration is not about moving data 

             it’s about preserving logic, relationships, and meaning.”

                                      Data Engineering Principle

Migration Flow Overview

Target Audience

• Data Engineers handling legacy report migrations
• BI Developers rebuilding reporting infrastructure
• Technical teams migrating .pbix files to enterprise databases
• Analysts documenting data dependencies before migration

Migration Phases

Step 1: Export Data from Power BI

Open your .pbix file in Power BI Desktop. Navigate to the Data View by clicking the table icon on the left sidebar. Right-click on each table and select “Copy Table” or use the “Export Data” option to save the contents as a CSV file.

• Record row counts: Document the number of rows in each table. This will be used later for validation after migration.
• Export hidden columns: Some columns may be hidden from the report view but still contain critical data. Ensure all columns are exported.
• Preserve exact table names: Use identical naming conventions to avoid breaking relationships and DAX calculations.
• Organize exported files: Create a dedicated folder for all CSV exports with subfolders per table if necessary.

Step 2: Load Data into SQL Server

Once CSV files are ready, the next step is to load them into a SQL Server database. This transforms flat files into a structured, queryable data source. Always define data types manually rather than relying on automatic detection, which can misclassify columns (e.g., treating dates as strings or integers as decimals).

-- Step 2.1: Create the target table with explicit data types
CREATE TABLE SalesOrders (
    OrderDate DATE,
    CustomerKey INT,
    ProductKey INT,
    SalesAmount DECIMAL(18,2)
);

-- Step 2.2: Import CSV data using BULK INSERT
BULK INSERT SalesOrders
FROM 'C:\MigrationData\SalesOrders.csv'
WITH (
    FIELDTERMINATOR = ',',    -- Comma-separated values
    FIRSTROW = 2,             -- Skip header row
    ROWTERMINATOR = '\n',     -- Line break as row delimiter
    DATAFILETYPE = 'char'
);

-- Step 2.3: Verify row count matches exported CSV
SELECT COUNT(*) AS ImportedRowCount FROM SalesOrders;

Professional guidance: For enterprise environments, consider using Azure Data Factory or SQL Server Integration Services (SSIS) for larger datasets. If you do not have a SQL Server instance, use SQL Server Developer Edition (free for development) or Azure SQL Database. The goal is to establish a centralized, governed data repository before proceeding to Fabric.

Step 3: Create a Data Pipeline in Microsoft Fabric

Microsoft Fabric provides a unified platform for data integration. A Data Pipeline automates the extraction, transformation, and loading (ETL) process from SQL Server to Fabric Warehouse or Lakehouse. This eliminates manual exports and enables scheduled refreshes.

1. Access Microsoft Fabric: Navigate to your Fabric workspace. Select “New” → “Data Pipeline” and provide a descriptive name (e.g., “Sales_Data_Migration”).
2. Add Copy Data Activity: Drag the “Copy Data” activity onto the canvas. This will be the core component moving data from source to destination.
3. Configure Source: Select SQL Server as the source. Provide connection details: server name, database name, authentication method. Choose the table(s) or write a SQL query to select specific columns.
4. Configure Destination: Choose Fabric Warehouse or Lakehouse as the destination. Map source columns to destination columns exactly — mismatched data types or names will cause failures.
5. Set Scheduling: Define a refresh schedule (daily, hourly, or on-demand) to keep the Fabric destination synchronized with SQL Server.
6. Validate and Run: Use the pipeline validation tool to check for errors. Execute a test run with a small dataset before full migration.

Step 4: Reconnect Power BI to Fabric Warehouse

The original Power BI report contains embedded data from the CSV exports. After data resides in Fabric, the report must be redirected to the new live connection. This step preserves all visuals, measures, and relationships while changing only the data source.

• Open the original .pbix file in Power BI Desktop.
• Navigate to Transform Data → Data Source Settings. Click “Change Source”.
• Select Fabric Warehouse as the new source. Enter your Fabric endpoint and database name.
• Remap tables: For each table, ensure the query points to the correct Fabric table name (e.g., SalesOrders). Replace any previously hardcoded CSV references.
• Update credentials and test the connection. Power BI will validate that all required columns exist.
• Refresh preview data to confirm visuals display correctly. All existing measures and relationships should remain intact if table and column names are unchanged.

Step 5: Validate and Publish the Refreshed Report

After reconnecting the report to Fabric, perform a comprehensive validation to ensure data integrity and visual accuracy. Do not skip this phase — subtle differences in data types or missing rows can cause incorrect business decisions.

• Row count verification: Compare row counts from step 1 with the new Fabric connection. They must match exactly.
• Column data type check: Verify that date columns are recognized as dates, numeric columns as numbers, and text columns as text. Use Power Query to inspect column profiles.
• Measure validation: Test key DAX measures (e.g., total sales, running totals, year-over-year growth) against known baseline numbers from the original report.
• Visual inspection: Review each report page. Look for blank visuals, error messages, or unexpected values.
• Relationship testing: Cross-filter between tables (e.g., click a product category and ensure sales visuals update correctly). Broken relationships reveal missing foreign keys or renamed columns.
• Performance assessment: Compare refresh times. Fabric-backed reports should load faster than CSV-based ones, especially with large datasets.
• Publish to Power BI Service: Once validation passes, publish the updated .pbix file to the Power BI Service. Configure scheduled refresh using Fabric’s data gateway (if required).

Final Validation Checklist

Common Migration Pitfalls and Solutions

• Data type mismatch: CSV exports may store dates as strings. Always use explicit conversion in SQL table definition (e.g., CAST or CONVERT).
• Missing rows due to header assumptions: Ensure the BULK INSERT FIRSTROW parameter matches your CSV structure (2 if first row is header).
• Special characters or delimiters: If data contains commas, use a different FIELDTERMINATOR (e.g., ‘|’) or quote identifiers.
• Case sensitivity differences: SQL Server may be case-sensitive while Power BI is not. Use consistent naming (e.g., all lowercase or PascalCase).
• Fabric pipeline permission errors: Verify that the service principal or user account has write access to both SQL Server (source) and Fabric Warehouse (destination).

Glossary of Terms