Power Bi

Power BI Development Skill

End-to-end Power BI project development using the PBIP (Power BI Project) format — the git-friendly, text-based format for version-controlled Power BI solutions.

When This Skill Applies

• Creating or modifying Power BI semantic models (TMDL files)
• Writing or debugging Power Query (M) expressions (.pq files)
• Authoring DAX measures
• Designing star schema data models
• Publishing reports to Power BI Service
• Configuring scheduled refresh and data gateways
• Troubleshooting PBI connectors (especially Azure Cost Management / EA)
• Working with any .tmdl, .pq, .pbip, .pbir, .pbism files

PBIP Project Structure

ProjectName/
├── ProjectName.pbip                    # Open this in PBI Desktop
├── ProjectName.Report/
│   ├── report.json                     # Report visuals (edit in PBI Desktop)
│   ├── definition.pbir                 # Report definition pointer
│   └── StaticResources/
│       └── SharedResources/BaseThemes/ # Custom themes (.json)
├── ProjectName.SemanticModel/
│   ├── definition.pbism                # Semantic model pointer
│   └── definition/
│       ├── model.tmdl                  # Model-level settings
│       ├── tables/                     # Table definitions + measures (.tmdl)
│       ├── expressions/                # Power Query scripts (.pq)
│       └── relationships.tmdl          # Star schema joins
├── scripts/                            # Python automation (optional)
└── docs/                               # Documentation

Workflow Routing

Reference Files

Read these as needed — don't load all at once:

Tools

File Operations (PBIP Development)

Code Search & Navigation

Automation & Scripting

Browser Automation (Power BI Service)

Research & Documentation

Data Inspection

Common Tool Sequences

Adding a new measure:

1. Grep for existing lineageTags → ensure uniqueness
2. Read _Measures.tmdl → understand patterns
3. Edit _Measures.tmdl → add the new measure

Adding a new table:

1. Write expressions/NewTable.pq → create PQ expression
2. Write tables/NewTable.tmdl → create table definition
3. Edit relationships.tmdl → add relationship
4. Grep for lineageTags → verify no conflicts

Troubleshooting connector auth:

1. Read workflows/connector-auth.md → get diagnosis steps
2. Browser tools → navigate to EA portal or PBI Service settings
3. Read project memory → check known EA enrollment details

Publishing and refresh:

1. Browser tools → navigate to PBI Service workspace
2. read_page → check current dataset settings
3. form_input → configure refresh schedule
4. get_page_text → verify refresh history

Critical Rules

1. PBIP format only — Never suggest .pbix for version-controlled projects. PBIP is the git-friendly format (TMDL = text, PQ = text, clean diffs).

2. Localization awareness — Ask the user what language their labels should be in. For Brazilian projects, use pt-BR labels, BRL currency format (R$ #,##0.00), and Portuguese month names.

3. Star schema discipline — All relationships must be M:1 from fact to dimension tables. Use crossFilteringBehavior: oneDirection. Use isActive: false + USERELATIONSHIP() in DAX for ambiguous paths (e.g., multiple date relationships).

4. Measures table pattern — All DAX measures go in a dedicated _Measures table (calculated table with ROW("MeasureColumn", 0)). Organize measures into displayFolder groups.

5. Power Query parameter — Use a Parameter_ExportFolder parameter for folder-based CSV ingestion. This makes the data source path configurable without editing PQ code.

6. lineageTag convention — Every table, measure, column, and relationship needs a unique lineageTag. Use descriptive kebab-case: m-custo-total, t-fact-usage, rel-usage-date.

7. Format strings — Currency: R$ #,##0.00 (or locale-appropriate). Percentage: 0.0%;-0.0%;0.0%. Integer: #,##0. Use the three-part format for percentages to handle negative values.

8. EA connector vs Azure RBAC — The PBI Cost Management connector for EA enrollments requires Enterprise Administrator (read-only) role at the billing account level. Standard Azure RBAC roles (Cost Management Reader, Billing Reader) do NOT work. See workflows/connector-auth.md.

Quick Examples

TMDL Measure

measure 'Custo Total' =
    SUM(Fact_Usage[PretaxCost])
    formatString: R$ #,##0.00
    displayFolder: Custo
    lineageTag: m-custo-total

TMDL Relationship

relationship Fact_Usage_to_Dim_Date
    fromColumn: Fact_Usage.DateKey
    toColumn: Dim_Date.Date
    crossFilteringBehavior: oneDirection

Power Query — Folder-based CSV ingestion

let
    Source = Folder.Files(Parameter_ExportFolder),
    Filtered = Table.SelectRows(Source, each
        Text.Contains([Name], "UsageDetails") and Text.EndsWith([Name], ".csv")),
    Combined = Table.Combine(
        Table.AddColumn(Filtered, "Data", each
            Csv.Document([Content], [Delimiter=",", Encoding=65001])
        )[Data]
    )
in
    Combined

DAX — Month-over-Month variance with safe division

measure 'Variacao MoM %' =
    VAR CustoAtual = [Custo Mes Atual]
    VAR CustoAnterior = [Custo Mes Anterior]
    RETURN
        IF(
            CustoAnterior <> 0,
            DIVIDE(CustoAtual - CustoAnterior, CustoAnterior),
            BLANK()
        )
    formatString: 0.0%;-0.0%;0.0%
    displayFolder: Variacao

Gotchas

• PBIP vs PBIX one-way regeneration: Re-saving a .pbip as .pbix and back can silently regenerate every lineageTag GUID and rewrite TMDL whitespace, producing a massive zero-semantic diff. Always round-trip through PBIP and review diffs before committing.
• EA connector ignores Azure RBAC: Cost Management Reader and Billing Reader roles always fail for EA enrollments. Only Enterprise Administrator (read-only) at billing account level works. MCA enrollments use a completely different auth path.
• Parameter_ExportFolder must be Text type, not a path literal: PQ accepts literals during dev but Service refresh evaluates parameters before credentials, throwing an opaque "formula.firewall" error that never names the offending parameter.
• Three-part format strings flip on zero: 0.0%;-0.0%;0.0% shows zero in positive form. Use 0.0%;-0.0%;"-" if zero should render as a dash — positives look identical in review.
• Folder.Files on a live export folder is non-deterministic: If exports overwrite the same filename mid-refresh, PQ may read a partial file with no error. Filter by Date.From([Date modified]) or partition by YYYYMM in the export path.
• crossFilteringBehavior: oneDirection is the only correct spelling: singleDirection parses on import but is silently ignored. Misspelling makes a star schema go bidirectional, killing performance and creating ambiguous paths.