power-bi-project-builder

name

power-bi-project-builder

description

Builds a complete, ready-to-open Power BI Project (.pbip) from an Excel data source — zero manual steps. Invoke this skill whenever the user wants to create, generate, or build a Power BI report or dashboard from an Excel file (.xlsx), CSV, or any tabular data file. Triggers include: "build a Power BI report", "create a pbip project", "make a dashboard from my Excel", "turn this data into a Power BI file", "generate a Power BI project", "I have an xlsx and want a Power BI report", "build me a CFO dashboard", or any request that involves generating a .pbip from scratch from data. Also use this skill when the user wants a Power BI dashboard — even if they don't mention Power BI explicitly but you can tell they need one.

Power BI Project Builder

You build complete, production-ready Power BI Projects (.pbip format) from Excel data files using only Python file generation — no GUI interaction required.

What You Produce

A .pbip project folder that opens directly in Power BI Desktop, containing:

• A Semantic Model (TMDL-based) with all tables, relationships, and DAX measures
• A Report with a single-page executive dashboard pre-populated with visuals

Workflow

STEP 1 — Analyze the Data

Read the Excel file using pandas. For every sheet, extract:

• Sheet names and row counts
• Column names and data types
• Sample values (5-10 rows)
• Any obvious relationships between sheets (matching column names)
• Date ranges (for time intelligence setup)

Use this analysis to decide the semantic model structure (fact tables, dimensions, measure groups).

STEP 2 — Design the Model

Based on the data, determine:

• Which sheets are fact tables (transactions, GL entries, sales records)
• Which sheets are dimension tables (COA, Calendar, Territory, Products, Employees)
• Which columns form relationships (foreign keys)
• What DAX measures make sense for the domain (financial KPIs, HR metrics, sales metrics)
• What visuals would be most useful for decision-makers

For financial data specifically, see references/financial-dax.md for standard P&L, Balance Sheet, and ratio measures.

STEP 3 — Generate the Project

Use Python to write all project files. See references/tmdl-patterns.md for exact TMDL syntax.

Project structure to create:

ProjectName/
├── ProjectName.pbip
├── ProjectName.Report/
│   ├── .platform
│   ├── definition.pbir
│   ├── .pbi/
│   │   └── localSettings.json
│   └── definition/
│       ├── report.json
│       ├── version.json
│       ├── pages/
│       │   ├── pages.json
│       │   └── [pageId]/
│       │       ├── page.json
│       │       └── visuals/
│       │           └── [visualId]/
│       │               └── visual.json
└── ProjectName.SemanticModel/
    ├── .platform
    ├── definition.pbism
    ├── .pbi/
    │   └── localSettings.json
    └── definition/
        ├── database.tmdl
        ├── model.tmdl
        ├── relationships.tmdl
        └── tables/
            ├── [FactTable].tmdl
            ├── [DimTable1].tmdl
            └── _Measures.tmdl

STEP 4 — Write Files with Python (NEVER PowerShell for file writing)

All files must be UTF-8 without BOM. Use this pattern exclusively:

def write(path, content):
    os.makedirs(os.path.dirname(path), exist_ok=True)
    with open(path, 'w', encoding='utf-8', newline='\n') as f:
        f.write(content)

PowerShell's Set-Content -Encoding UTF8 writes UTF-8 WITH BOM which breaks Power BI. Never use it.

STEP 5 — Run the Delivery Checklist

Before reporting the project as complete, run the automated checks from references/delivery-checklist.md. This catches all known failure modes without having to open Power BI Desktop.

The build script (scripts/build_pbip.py) already embeds these checks in its DELIVERY CHECKS section. When using a custom script, copy those checks verbatim. All items must pass:

OK   No BOM detected
OK   Sales Dashboard.pbip → $schema = 'pbipProperties'
OK   definition.pbism → version = '4.2'
OK   report.json → $schema = 'report/3.0.0'
OK   report.json → themeCollection → CY25SU10
OK   version.json → $schema = 'versionMetadata'
OK   version.json → version = '2.0.0'
OK   pages.json → $schema = 'pagesMetadata/1.1.0'
OK   pages.json → activePageName = 'pageId'
OK   page.json → $schema = 'page/2.0.0'
OK   TMDL tables: Sales.tmdl, Product.tmdl, _Measures.tmdl ...

Any FAIL line must be fixed before handing off to the user.

STEP 6 — Verify and Guide

Tell the user:

1. The full path to the generated .pbip file
2. How to open it (double-click or File > Open in Power BI Desktop)
3. What to expect (which visuals, which measures, what data)

---

Critical Rules

Read references/common-errors.md before writing any TMDL file. These errors will crash the project on load.

TMDL errors:

1. Reserved table name "Measures" → always name the measures table _Measures
2. ref table must be at root level in model.tmdl — not indented inside the model block
3. isDateTable is not valid as a standalone TMDL property — omit it entirely
4. UTF-8 BOM from PowerShell file writing — use Python open() only
5. Missing $schema in both localSettings.json files
6. defaultPowerBIDataSourceVersion must be in the model header

Schema version errors (June 2026): 7. .pbip must use pbipProperties not pbipManifest in $schema URL 8. definition.pbism must have "version":"4.2" and "settings":{} — version 1.0 causes "model.bim missing" error 9. Report schemas must be June 2026 versions — old CY24SU10/5.58 schemas cause JavaScript render crash:

• report.json → report/3.0.0, theme CY25SU10, include reportVersionAtImport
• version.json → versionMetadata/1.0.0, "version":"2.0.0"
• pages.json → pagesMetadata/1.1.0, include activePageName
• page.json → page/2.0.0

---

TMDL Quick Reference

See references/tmdl-patterns.md for complete syntax. Key patterns:

model.tmdl (EXACT format required)

model Model
	culture: en-US
	defaultPowerBIDataSourceVersion: powerBI_V3
	sourceQueryCulture: en-US
	dataAccessOptions
		legacyRedirects
		returnErrorValuesAsNull

	annotation __PBI_TimeIntelligenceEnabled = 1

	annotation PBI_QueryOrder = ["GL","COA","Calendar","_Measures"]

ref table GL
ref table COA
ref table Calendar
ref table _Measures

Note: annotation lines are indented (inside model block). ref table lines are NOT indented (root level).

_Measures.tmdl

table _Measures
	lineageTag: [uuid]

	measure 'Measure Name' =
			CALCULATE(SUM(Table[Column]), Filter)
		formatString: $#,0;($#,0);$#,0
		lineageTag: [uuid]

	partition _Measures = m
		mode: import
		source =
				let
				    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
				    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
				    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1"})
				in
				    #"Removed Columns"

	annotation PBI_ResultType = Table

---

Dashboard Design Principles

• Single page for executive dashboards (decision-makers scan, not drill)
• Page size: 1280×720 (standard widescreen)
• Standard layout: KPI cards at top → charts in middle → slicers on side/top
• Visual count: 8-15 visuals (6 KPI cards + 4-6 charts + 2 slicers)
• Color scheme: Professional blues (#1a3a5c, #4a6fa5, #f0f4f8)

For visual JSON templates, see references/visual-templates.md.

---

Reference Files

• references/tmdl-patterns.md — Complete TMDL syntax + all required JSON schemas (June 2026 validated)
• references/common-errors.md — All 9 known Power BI errors with fixes + quick diagnostic checklist
• references/delivery-checklist.md — Full project delivery checklist (run before every handoff)
• references/visual-templates.md — JSON templates for cardVisual, columnChart, lineChart, barChart, donutChart, slicer
• references/financial-dax.md — Standard financial measures (P&L, Balance Sheet, ratios)
• scripts/build_pbip.py — Complete Python generator script with embedded delivery checks