From Raw Data to Stunning Report

Why we start with the lakehouse

Yes, I know, starting a Power BI series without opening Power BI Desktop feels wrong. But all started with a customer trying to build that "beautiful report" straight from CSVs in Blob and a product table in SQL. It worked for a week, then broke: two definitions of sales, slow refreshes, and no one knew which ProductKey was right.

Enterprises usually want what my customer wanted here too, one copy of the data with real access control, not five copies emailed around. That's why we start in Fabric, not in Desktop. This series follows the order I wish I'd shown them on day one:

To start I'm assuming you already have a Fabric workspace with a trial or F2 capacity, if you don't, get one first, the rest won't work without it. Learn more here: Fabric trial docs. Notice I'm talking about the Fabric lakehouse of today (OneLake + shortcuts), not the old ADLS Gen2 + Data Factory V1 pattern, the concepts rhyme, the clicks don't.

A Fabric Lakehouse on OneLake works a bit like those Service Principals from my old SQL article, one identity, one place to grant access, instead of credentials scattered everywhere. Notebooks, pipelines, warehouses and Power BI all see the same files. GitHub Copilot, through Fabric MCP, just writes the boring ingestion code for you.

Set up Fabric MCP + Copilot

You need a Fabric capacity (trial works), a GitHub Copilot subscription, and VS Code with the Fabric MCP Server extension. No service principal needed for local work, the extension uses your signed-in user identity.

Option 1: VS Code extension (recommended)

1. Install GitHub Copilot Chat + Microsoft Fabric MCP Server extensions
2. Run Azure: Sign In from the Command Palette (or az login in terminal)
3. Switch Copilot to Agent mode, refresh tools, Fabric tools appear. Done.

The extension is local-first and registers itself automatically, no mcp.json secrets to manage.

Option 2: Manual npx setup (any MCP client)

# Requires Node 20+
npx -y @microsoft/fabric-mcp@latest server start --mode all

// .vscode/mcp.json, minimal, user auth via Azure CLI
{
  "servers": {
    "fabric": {
      "type": "stdio",
      "command": "npx",
      "args": ["-y", "@microsoft/fabric-mcp@latest", "server", "start", "--mode", "all"],
      "env": {
        "FABRIC_WORKSPACE_ID": "${env:FABRIC_WORKSPACE_ID}"
      }
    }
  }
}

// MCP Call: fabric.listOneLakeItems(workspaceId)

Create your lakehouse & folder structure

// MCP Call: fabric.createLakehouse(workspaceId, { name: "SalesLakehouse" })
// MCP Call: fabric.createFolders(workspaceId, ["Files/bronze","Files/silver","Files/gold","Files/notebooks"])

📁 SalesLakehouse/Files/
  📁 bronze/raw_sales    ← Source A shortcut to Blob CSV (bronze as shortcut)
  📁 bronze/dim_product  ← Source B Delta snapshot from Azure SQL
  📁 silver/             ← cleaned + joined tables (Part 2)
  📁 gold/               ← star schema (Part 2)
  📁 notebooks/          ← engineering code (Part 2)

Source A (shortcut): sales CSVs as bronze, zero-copy

Sales transactions live as daily CSVs in Azure Blob (sales-raw container). They're immutable drops, large, and already partitioned by date, ideal for a OneLake shortcut. The shortcut is our bronze for this source: no copy, always fresh. We go straight shortcut to Silver in Part 2.

// MCP Call: fabric.createOneLakeShortcut({
  lakehouse: "SalesLakehouse",
  path: "Files/bronze/raw_sales",
  source: { type: "BlobStorage", container: "sales-raw", path: "daily/" }
})

Source B (ingested): SQL product master to bronze Delta

Product details live in Azure SQL (sql-contoso-prod.database.windows.net / SalesDW / dim.Product) with ProductKey, Category, Subcategory, Brand, UnitCost. We deliberately do NOT shortcut this one:

• Small dimension, copy is cheap, query is fast as Delta
• OLTP source, we don't want Spark scans hammering it; take a daily snapshot
• Needs history/offline availability even if SQL is down
• Joinable to Source A on ProductKey, the bridge between the two sources

// MCP Call: fabric.createPipeline(workspaceId, {
  name: "Bronze Product Snapshot",
  activities: [{
    type: "Copy",
    source: { type: "AzureSQL", server: "sql-prod.database.windows.net",
      database: "SalesDW", table: "dim.Product" },
    sink: { type: "OneLakeDelta", table: "Files/bronze/dim_product",
      writeMode: "Overwrite" }
  }],
  schedule: "0 3 * * *",
  retry: { attempts: 2, intervalMin: 15 }
})

Verify both sources + join keys with Copilot

// MCP: fabric.readCSV(ws, "Files/bronze/raw_sales/daily/2026/09/15/sales.csv", { sample: 5 })
// MCP: fabric.readDeltaTable(ws, "Files/bronze/dim_product", { sample: 5 })

The medallion pattern in 2 minutes

One of my customers asked the obvious question here: "If the shortcut already shows the CSVs, why copy anything?" Fair. The shortcut is our bronze for sales, zero copy. The SQL snapshot is bronze for products because you don't want Spark hammering your OLTP every run. Same word "bronze", two very different mechanisms under the hood. To start I'm assuming Part 1's checks passed, if you still have null keys, fix those first, the join below won't save you.

Bronze preserves truth. Silver fixes types, dedupes, handles returns. Gold reshapes into facts + dimensions, exactly what a Power BI semantic model loves. Fabric MCP lets Copilot read, write, and execute all of this from chat.

Silver: join shortcut + SQL snapshot with a Copilot notebook

Source A is CSV-via-shortcut (no Delta), Source B is Delta. Silver is where they meet: read both, type both, left-join sales to product on ProductKey, keep match flag for quarantine.

# Files/notebooks/silver_cleaning.ipynb
from pyspark.sql import functions as F

# Source A: shortcut CSVs, first materialization happens here
sales_raw = (spark.read.option("header", True)
    .csv("Files/bronze/raw_sales/daily/*/*/*/sales.csv")
    .withColumn("Quantity", F.col("Quantity").cast("int"))
    .withColumn("UnitPrice", F.col("UnitPrice").cast("decimal(10,2)"))
    .withColumn("SalesAmount", F.col("SalesAmount").cast("decimal(18,2)")))

# Source B: SQL snapshot Delta from Part 1
products = spark.read.format("delta").load("Files/bronze/dim_product")

sales = (sales_raw
    .dropDuplicates(["SalesKey"])
    .filter(F.col("SalesKey").isNotNull())
    .withColumn("Date", F.to_date("DateKey", "yyyyMMdd"))
    .withColumn("IsReturn", F.col("SalesAmount") < 0)
    .withColumn("SalesAmount", F.abs("SalesAmount")))

# The join: sales LEFT JOIN product master
enriched = (sales.join(products.select("ProductKey", "Category",
        "Subcategory", "Brand", "UnitCost"), "ProductKey", "left")
    .withColumn("HasValidProduct", F.col("Category").isNotNull())
    .withColumn("PartitionYM", F.date_format("Date", "yyyyMM")))

enriched.write.format("delta").mode("overwrite") \
    .partitionBy("PartitionYM").save("Files/silver/clean_sales")

print("silver written (shortcut + SQL joined)")

// MCP Call: fabric.runNotebook(ws, "silver_cleaning.ipynb")  to  2m 41s [ok]

Data quality + quarantine (including join orphans)

Never silently drop rows, especially failed joins. The 32 sales with unknown ProductKey from Part 1 go here with reason unknown_product, alongside nulls and future dates.

checks = (clean
    .withColumn("fail_reason",
        F.when(F.col("Date") > F.current_date(), "future_date")
         .when(F.col("ProductKey").isNull(), "null_product")
         .otherwise(None)))

quarantine = checks.filter("fail_reason IS NOT NULL")
quarantine.withColumn("quarantined_at", F.current_timestamp()) \
    .write.format("delta").mode("append").save("Files/silver/_quarantine")

good = checks.filter("fail_reason IS NULL")
print(f"good={good.count()} quarantined={quarantine.count()}")

Gold: star-schema tables (DimProduct from SQL source)

Gold is deliberately boring: one fact, conformed dimensions. DimProduct comes from authoritative Source B (SQL snapshot), not re-derived from sales, so categories stay complete even for slow-moving products with no sales this period.

# Files/notebooks/gold_star_schema.ipynb
silver = spark.read.format("delta").load("Files/silver/clean_sales") \
    .filter("HasValidProduct AND fail_reason IS NULL")
products_bronze = spark.read.format("delta").load("Files/bronze/dim_product")

# Dimensions, DimProduct authoritative from SQL snapshot
dim_date = silver.select("Date").distinct() \
    .withColumn("DateKey", F.date_format("Date","yyyyMMdd").cast("int")) \
    .withColumn("Year", F.year("Date")).withColumn("Month", F.month("Date"))

dim_product = products_bronze.select(
    "ProductKey","Category","Subcategory","Brand","UnitCost").distinct()
dim_customer = silver.select("CustomerKey").distinct() \
    .withColumn("Segment", F.lit("Standard"))
dim_store = silver.select("StoreKey","Region","StoreName").distinct()

fact = silver.join(dim_date, "Date", "left") \
    .select("SalesKey","DateKey","ProductKey","CustomerKey","StoreKey",
            "Quantity","UnitPrice","SalesAmount","IsReturn")

for name, df in {"FactSales": fact, "DimDate": dim_date,
                 "DimProduct": dim_product, "DimCustomer": dim_customer,
                 "DimStore": dim_store}.items():
    df.write.format("delta").mode("overwrite") \
      .save(f"Files/gold/star_schema/{name}")
print("gold star schema: fact joined cleanly, DimProduct from SQL master")

Schedule it as a pipeline

// MCP Call: fabric.createPipeline(ws, {
  name: "Medallion Daily",
  activities: [
    { type: "Notebook", notebook: "silver_cleaning.ipynb" },
    { type: "Notebook", notebook: "gold_star_schema.ipynb",
      dependsOn: "silver_cleaning.ipynb" }
  ],
  schedule: "0 3:30 * * *", onFailureAlert: true
})

Why semantic models are important

Pointing Power BI at gold tables works, until it doesn't. My customer provides services to financial institutions, so "two versions of sales" isn't a nit, it's an audit finding. Notice I'm talking about the DirectLake semantic model of today, not the old Import-only PBIX pattern. Under the hood it's the same lesson as EmbedToken: the token (or measure) you see is derived from something upstream, get the upstream wrong and everything downstream inherits it. A model fixes three things at once:

Connect Power BI MCP

Fabric MCP got data in. Power BI MCP lets Copilot read and write the model: discover tables, generate DAX, run queries, apply themes. Same as Part 1, your user login is enough, no service principal.

// .vscode/mcp.json, add alongside fabric, user auth
{
  "servers": {
    "fabric": { "...": "see Part 1" },
    "powerbi": {
      "type": "stdio",
      "command": "npx",
      "args": ["@microsoft/powerbi-mcp-server@latest"],
      "env": {
        "PBI_WORKSPACE_ID": "${env:PBI_WORKSPACE_ID}"
      }
    }
  }
}

Build the model on gold (DirectLake)

// MCP Call: powerbi.createDataset(ws, {
  name: "Sales Star Schema", mode: "DirectLake",
  source: "SalesLakehouse/Files/gold/star_schema/",
  tables: ["FactSales","DimDate","DimProduct","DimCustomer","DimStore"],
  relationships: [
    "DimDate[DateKey] to FactSales[DateKey]",
    "DimProduct[ProductKey] to FactSales[ProductKey]",
    "DimCustomer[CustomerKey] to FactSales[CustomerKey]",
    "DimStore[StoreKey] to FactSales[StoreKey]"
  ]
})

DAX measures with Copilot

Tables are scaffolding. Measures are the product. Let Copilot draft them, then validate.

Total Sales = SUMX(FactSales, FactSales[Quantity] * FactSales[UnitPrice] - FactSales[DiscountAmount])

Total Quantity = SUM(FactSales[Quantity])

YoY Growth % =
VAR CurrentSales = [Total Sales]
VAR PriorSales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DimDate[Date]))
RETURN DIVIDE(CurrentSales - PriorSales, PriorSales)

Gross Margin % =
DIVIDE([Total Sales] - SUM(FactSales[CostAmount]), [Total Sales])

// MCP Call: powerbi.addMeasures(ws, datasetId, measures)
// MCP Call: powerbi.runDax(ws, 'EVALUATE ROW("YoY", [YoY Growth %])') to 0.152 [ok]

Secure it: RLS in 5 minutes

// TMDL role: regions see only their rows
role RegionalSales
{
    modelPermission: read;
    tablePermission DimStore
    {
        filterExpression: DimStore[Region] = USERPRINCIPALNAME();
    }
}

// MCP: powerbi.runDax(ws, query, { effectiveUser: "na-lead@contoso.com" }) to $4.2M
// MCP: powerbi.runDax(ws, query, { effectiveUser: "eu-lead@contoso.com" }) to $2.8M

Why PBIP for local development

All started with a customer editing PBIX files back and forth over email, you can guess how that ended. .pbix is a binary blob, no diffs, no reviews. PBIP (the project format of today, not the old PBIX-only flow) is just text files you can version and generate with Copilot. To start I'm assuming your Part 3 model validated, if RLS isn't tested yet, go back, the Bridge screenshots below won't catch a security hole.

SalesReport.pbip/
├── SalesReport.pbiproj
├── definition/        ← points at Part 3 model (DirectLake)
└── report/
    ├── report.json
    ├── pages/         ← Copilot generates these
    └── visuals/

Scaffold the PBIP project

# VS Code extensions
code --install-extension ms-powerbi.powerbi-pbip
code --install-extension github.copilot
code --install-extension github.copilot-chat

# Convert or init
pbi-tools convert -input "SalesReport.pbix" -output "SalesReport.pbip" -format PBIP
# ...or init fresh and point dataset at Part 3 model ID

Generate pages + visuals with Copilot

// MCP: powerbi.createReportPage(ws, datasetId, pageSpec)
// report/pages/Overview.reportpage.json
{
  "name": "Overview", "displayName": "Executive Overview",
  "visualContainers": [
    { "name": "kpi-sales", "visualType": "card", "queryRef": "TotalSales" },
    { "name": "kpi-yoy", "visualType": "card", "queryRef": "YoYGrowth" },
    { "name": "bar-category", "visualType": "clusteredBarChart",
      "queryRef": "SalesByCategory" },
    { "name": "line-trend", "visualType": "lineChart",
      "queryRef": "MonthlyTrend" }
  ]
}

Bind data + add interactions

// MCP: powerbi.bindVisual(ws, reportId, "kpi-yoy", {
  measure: "YoY Growth %",
  conditionalFormatting: [
    { condition: "> 0", color: "#00875A" },
    { condition: "< 0", color: "#DE350B" }
  ]})
// MCP: powerbi.addDrillthrough(ws, reportId, "bar-category", "Product")

Live preview with Desktop Bridge: reload + screenshot

Editing PBIP JSON in VS Code is only half the loop, you need to see it. The Power BI Desktop Bridge (Preview) lets Copilot and external tools talk to a running Power BI Desktop on your machine, so you never restart to preview.

Bridge CLI cheat sheet

# Install once
npm install -g @microsoft/powerbi-desktop-bridge-cli

powerbi-desktop status        # which Desktop window / file / unsaved changes / pages
powerbi-desktop manifest      # which bridge methods this Desktop version supports
powerbi-desktop open ./SalesReport.pbip   # open PBIP and wait for bridge
powerbi-desktop reload        # reload PBIR from disk after VS Code edits
powerbi-desktop screenshot Overview       # capture one page as PNG
powerbi-desktop screenshot-all            # capture every page in pages.json

The Copilot edit to reload to screenshot loop

# 1. check state first (avoid clobbering unsaved work)
powerbi-desktop status
# currentFilePath: .../SalesReport.pbip, hasUnsavedChanges: false [ok]

# 2. reload report from disk (model too by default)
powerbi-desktop reload

# 3. visual check
powerbi-desktop screenshot Overview
# to Overview.png, agent "sees" card positions, flags overlap

How it maps to this series: Fabric MCP = cloud data APIs. Power BI Modeling MCP = model editing. Bridge = controlling the local Desktop renderer. Use all three in Part 4, then reuse screenshot-all in Part 5 for themed before/after proof.

Deploy with GitHub Actions, this is where you need a service principal

Local work used your user login. GitHub Actions can't click "Sign in", so here, and only here in this series, create a service principal: Entra app registration to secret to enable Service principals can use Fabric APIs in Admin portal to add SP to workspace as Admin/Member to store AZURE_CLIENT_ID / SECRET / TENANT_ID as GitHub secrets.

# .github/workflows/deploy-pbip.yml (simplified)
name: Deploy PBIP
on:
  push: { branches: [main] }
jobs:
  deploy:
    runs-on: ubuntu-latest
    environment: test
    steps:
      - uses: actions/checkout@v4
      - uses: azure/login@v2
        with:
          client-id: ${{ secrets.AZURE_CLIENT_ID }}
          tenant-id: ${{ secrets.AZURE_TENANT_ID }}
          subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}
      - name: Deploy PBIP
        uses: microsoft/powerbi-deploy-action@v1
        with:
          pbip-path: 'SalesReport.pbip'
          workspace-id: ${{ secrets.PBI_WORKSPACE_ID }}

Merge to main to Test workspace updates. Prod stays manual approval, we'll harden that in the finale's checklist.

Where we are: the journey so far

But going back to the question my customer had: "How do we make this look like us, not like default Power BI?" Same pattern as EmbedToken years ago, the beautiful part is derived from something upstream. There, the EmbedToken inherited AAD lifetimes. Here, the report inherits Figma tokens. Get the upstream right and the rest follows. This was confirmed to me the same way, by watching a designer change one token and the whole report follow.

Data flows nightly, the model is certified, the report is correct, but still on the default theme. Designers already solved beauty in Figma. We just have to pipe it through.

Connect Figma MCP

// .vscode/mcp.json, add third server
{
  "servers": {
    "fabric": { "...": "Part 1" },
    "powerbi": { "...": "Part 3" },
    "figma": {
      "type": "stdio",
      "command": "npx",
      "args": ["@figma/mcp-server@latest"],
      "env": {
        "FIGMA_ACCESS_TOKEN": "${env:FIGMA_ACCESS_TOKEN}",
        "FIGMA_FILE_KEYS": "${env:FIGMA_FILE_KEYS}"
      }
    }
  }
}

Get a token via Figma Settings to Personal Access Tokens (file:read, library:read), and copy the file key from your design-system URL.

// MCP: figma.getTeamLibraries() to Enterprise DS v3.2 (245 components)
// MCP: figma.getFileStyles(fileKey, "FILL") to 89 color styles

Tokens to Power BI theme with Copilot

{
  "name": "Enterprise Design System",
  "dataColors": ["#0052CC","#00875A","#FF8B00","#DE350B","#611F69","#007B83"],
  "background": "#FFFFFF",
  "foreground": "#172B4D",
  "tableAccent": "#0052CC",
  "visualStyles": {
    "*": { "card": [{
      "background": {"color": {"solid": {"color": "#FFFFFF"}}},
      "border": {"color": {"solid": {"color": "#DFE1E6"}}, "radius": 8},
      "shadow": {"color": {"solid": {"color": "#091E421A"}}, "blur": 8, "offsetY": 2}
    }]}
  }
}

// MCP: powerbi.applyTheme(ws, reportId, themeJson)
Theme "Enterprise Design System" applied to Sales Report

Apply + polish the report

// MCP: figma.getFrameNodes(fileKey, "Desktop - Full HD")
// MCP: powerbi.updateReportLayout(ws, reportId, "Overview", layoutSpec)
// MCP: powerbi.bindVisual(ws, reportId, "kpi-yoy", {
  measure: "YoY Growth %",
  conditionalFormatting: [{"> 0": "#00875A"}, {"< 0": "#DE350B"}] })

Publish + production checklist

Beauty without guardrails is a demo. Ship it like prod:

• PBIP validates clean; DAX measures pass 2 test cases each
• RLS tested per persona (Part 3 roles still enforced after theme change)
• Visual regression snapshots updated to themed pages
• Figma token sync Action scheduled; theme version pinned in repo
• Automation SP least-privilege; secrets in Key Vault / OIDC (local dev stays user-auth)
• Prod deploy requires approval; refresh + anomaly agents alerting

I hope this helps someone with the same lakehouse-to-report mess I had. I also want to thank the Fabric and Power BI product folks whose docs and Bridge samples made this verifiable instead of hand-wavy.

As usual, send me a note if you need to know more about this. Thank you!