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.

Diagnosis: Power BI Cost Management Connector — Access Denied

Root Cause

Rogerio's Cost Management Reader (Azure RBAC) role does NOT work for the EA connector. The EA connector authenticates against the EA Billing layer, which is a completely separate permission system from Azure RBAC.

Required Role

Enterprise Administrator (read-only) at the EA billing account level, granted through ea.azure.com.

Fix Steps

1. An existing EA Admin signs into https://ea.azure.com 2. Navigate to Manage > Enrollment tab 3. Click + Add Administrator 4. Enter Rogerio's email 5. Select Read Only checkbox 6. Click Add 7. Wait up to 30 minutes for propagation 8. In Power BI Desktop: File > Options > Data Source Settings > Clear Permissions 9. Retry the connection

Alternative

If EA Admin role cannot be granted, use Cost Management Exports to Azure Blob Storage + Blob connector in PBI. This only requires Cost Management Contributor (Azure RBAC) + storage access.

Why RBAC Doesn't Work

Diagnosis: Power BI Cost Management Connector — Access Denied

Root Cause

The Power BI Cost Management connector for EA enrollments does NOT use Azure RBAC roles. When Rogerio enters enrollment number 53329720, the connector authenticates against the EA Billing API, which has its own separate permission system managed through the EA Portal (ea.azure.com). His "Cost Management Reader" role on the subscription is completely irrelevant to this authentication path.

Fix

Rogerio needs an Enterprise Administrator (Read-Only) role assigned at the EA enrollment level in the EA Portal. An existing Enterprise Admin must grant this. After assignment, wait ~30 minutes for propagation, then retry.

DAX Patterns Reference

Common DAX measure patterns for Power BI, with examples from an Azure FinOps project (BRL, pt-BR).

Measures Table Pattern

All measures in a dedicated _Measures calculated table:

table _Measures
    lineageTag: measures-001

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

    column MeasureColumn
        dataType: int64
        isHidden: true
        lineageTag: measures-col
        summarizeBy: none
        sourceColumn: none

    partition _Measures = calculated
        source = ROW("MeasureColumn", 0)

Core Aggregation

Simple SUM

measure 'Custo Total' = SUM(Fact_Usage[PretaxCost])

CALCULATE with USERELATIONSHIP

For inactive relationships (multiple fact tables sharing a dimension):

measure 'Custo Marketplace' =
    CALCULATE(
        SUM(Fact_Marketplace[PretaxCost]),
        USERELATIONSHIP(Fact_Marketplace[DateKey], Dim_Date[Date])
    )

Combining Measures

measure 'Custo Combinado' = [Custo Total] + [Custo Marketplace]

Time Intelligence

Month-to-Date

measure 'Custo Mes Atual' =
    CALCULATE([Custo Combinado], DATESMTD(Dim_Date[Date]))

Previous Month

measure 'Custo Mes Anterior' =
    CALCULATE([Custo Combinado], PREVIOUSMONTH(Dim_Date[Date]))

Same Period Last Year

measure 'Custo Mesmo Mes Ano Anterior' =
    CALCULATE([Custo Combinado], SAMEPERIODLASTYEAR(Dim_Date[Date]))

Year-to-Date

measure 'Custo YTD' =
    CALCULATE([Custo Combinado], DATESYTD(Dim_Date[Date]))

YTD vs Prior Year YTD

measure 'Custo YTD Ano Anterior' =
    CALCULATE([Custo YTD], SAMEPERIODLASTYEAR(Dim_Date[Date]))

Safe Division & Variance

Always use VAR/RETURN with DIVIDE or IF for safe division:

Month-over-Month %

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

Year-over-Year %

measure 'Variacao YoY %' =
    VAR CustoAtual = [Custo Combinado]
    VAR CustoAnoAnterior = [Custo Mesmo Mes Ano Anterior]
    RETURN
        IF(
            CustoAnoAnterior <> 0,
            DIVIDE(CustoAtual - CustoAnoAnterior, CustoAnoAnterior),
            BLANK()
        )

Budget Utilization %

measure 'Utilizacao Orcamento %' =
    IF([Orcamento] <> 0, DIVIDE([Custo Combinado], [Orcamento]), BLANK())

Rolling Statistics

3-Month Moving Average

measure 'Media Movel 3M' =
    AVERAGEX(
        DATESINPERIOD(Dim_Date[Date], MAX(Dim_Date[Date]), -3, MONTH),
        CALCULATE([Custo Combinado])
    )

Standard Deviation (for anomaly detection)

VAR Dates3M = DATESINPERIOD(Dim_Date[Date], MAX(Dim_Date[Date]), -3, MONTH)
VAR StdDev = STDEVX.P(Dates3M, CALCULATE([Custo Combinado]))

Anomaly Detection

Flag costs that deviate significantly from the rolling average:

Anomaly Flag (>2 std dev)

measure 'Eh Anomalia' =
    VAR CurrentCost = [Custo Combinado]
    VAR AvgCost = [Media Movel 3M]
    VAR Dates3M = DATESINPERIOD(Dim_Date[Date], MAX(Dim_Date[Date]), -3, MONTH)
    VAR StdDev = STDEVX.P(Dates3M, CALCULATE([Custo Combinado]))
    RETURN
        IF(AND(StdDev > 0, ABS(CurrentCost - AvgCost) > 2 * StdDev), 1, 0)

Severe Anomaly (>3 std dev)

Same pattern but > 3 * StdDev.

Anomaly Impact

measure 'Impacto Anomalia' =
    IF([Eh Anomalia] = 1, [Custo Combinado] - [Media Movel 3M], BLANK())

Budget Measures

Budget with USERELATIONSHIP

measure 'Orcamento' =
    CALCULATE(
        SUM(Dim_Budget[BudgetAmount]),
        USERELATIONSHIP(Dim_Budget[BillingDate], Dim_Date[Date])
    )

Budget Variance

measure 'Variancia Orcamento' = [Orcamento] - [Custo Combinado]

Tag Compliance

Percentage of compliant resources

measure 'Recursos Tagueados %' =
    DIVIDE(
        COUNTROWS(FILTER(Dim_ResourceGroup, Dim_ResourceGroup[TagsCompletas] = TRUE())),
        COUNTROWS(Dim_ResourceGroup)
    )

Cost of non-compliant resources

measure 'Custo Sem Tag' =
    CALCULATE([Custo Total], FILTER(Dim_ResourceGroup, Dim_ResourceGroup[TagsCompletas] = FALSE()))

Per-tag compliance

measure 'Conformidade Executivo %' =
    DIVIDE(
        COUNTROWS(FILTER(Dim_ResourceGroup, Dim_ResourceGroup[TemTagExecutivo] = TRUE())),
        COUNTROWS(Dim_ResourceGroup)
    )

Projection / Forecasting

Daily Average (current month)

measure 'Media Diaria Mes' =
    VAR TotalCost = [Custo Mes Atual]
    VAR DaysElapsed = COUNTROWS(
        FILTER(DATESMTD(Dim_Date[Date]), Dim_Date[Date] <= TODAY())
    )
    RETURN DIVIDE(TotalCost, DaysElapsed)

End-of-Month Projection

measure 'Projecao Fim Mes' =
    VAR DailyAvg = [Media Diaria Mes]
    VAR DaysInMonth = DAY(EOMONTH(MAX(Dim_Date[Date]), 0))
    RETURN DailyAvg * DaysInMonth

Conditional Formatting Helpers

Return hex colors for visual formatting:

measure 'Cor Variacao' =
    IF([Variacao MoM %] > 0.05, "#E74C3C",
    IF([Variacao MoM %] < -0.05, "#27AE60",
    "#F39C12"))

• Red (#E74C3C): Cost increased >5%
• Green (#27AE60): Cost decreased >5%
• Yellow (#F39C12): Stable (within 5%)

Balance Measures (EA Commitment)

measure 'Saldo Compromisso EA' =
    CALCULATE(
        MAX(Fact_Balance[EndingBalance]),
        USERELATIONSHIP(Fact_Balance[BillingDate], Dim_Date[Date]),
        LASTDATE(Dim_Date[Date])
    )

Placeholder Measures

For features not yet implemented:

measure 'Cobertura RI %' =
    BLANK()
    formatString: 0.0%
    displayFolder: Reservas
    lineageTag: m-cobertura-ri
    annotation note = "Placeholder - requires Reservation Details export data"

Format String Reference

Three-part percentage format: positive;negative;zero — handles all cases.

Power BI Service & Gateway Reference

Publishing from Desktop

1. Open .pbip in Power BI Desktop 2. Home > Publish 3. Select workspace (or create new) 4. If replacing existing: confirm overwrite 5. Dataset and report are published as separate items

After publishing, the dataset (semantic model) and report are independent — you can update one without the other.

On-Premises Data Gateway

When Needed

• Data source is local files (CSV folder, Excel)
• Data source is on-premises database (SQL Server, Oracle)
• Data source is on a network share
• Data source requires VPN or private network

Not Needed

• Cloud data sources (Azure SQL, Azure Blob Storage, Dataverse)
• Azure Cost Management connector
• SharePoint Online / OneDrive

Installation

1. Download: https://powerbi.microsoft.com/gateway/ 2. Install on a server (not a laptop):

• Always-on, reliable internet
• Access to all data sources
• .NET Framework 4.7.2+

3. Sign in with Power BI account 4. Register gateway name in tenant

Gateway Modes

For enterprise: always Standard.

Adding Data Sources

1. Power BI Service > Settings > Manage gateways 2. Select gateway > Add data source 3. Configure source type (Folder, SQL Server, etc.) 4. Set credentials 5. Test connection

Scheduled Refresh

Setup

1. Power BI Service > Workspace > Dataset > Settings 2. Gateway connection: map each source to gateway data source 3. Scheduled refresh: toggle ON 4. Set frequency: Daily at 08:00 (typical for FinOps) 5. Set timezone 6. Enable failure notifications 7. Apply

Refresh Limits

Refresh History

Dataset > Refresh history — shows last N refreshes with:

• Start time, duration
• Status (success/failure)
• Error details for failures

Power BI Template Apps

Template apps are pre-built PBI solutions that connect to specific services.

Azure Cost Management Template App

1. Power BI Service > Apps > Get apps 2. Search "Azure Cost Management" 3. Install > Connect 4. Enter parameters:

• Scope: Enrollment Number
• Enrollment ID: 53329720
• Number of months: 13 (or desired range)

5. Sign in with organizational account (needs EA Admin role) 6. Wait for initial data load

Template App vs Custom PBIP

Dataset Settings

Parameters

• View and update parameter values (e.g., Parameter_ExportFolder)
• Must match gateway data source path

Data Source Credentials

• OAuth2: organizational account (Azure AD)
• Key: account key or SAS token (storage)
• Basic: username/password (SQL)

Endorsement

• Promoted: recommended dataset
• Certified: verified by admin

Workspace Roles

Key differences:

• Admin: manage workspace settings, add/remove members
• Member: publish content, share items
• Contributor: publish content (cannot share)
• Viewer: consume only

REST API Quick Reference

Trigger Refresh

POST https://api.powerbi.com/v1.0/myorg/groups/{workspace-id}/datasets/{dataset-id}/refreshes
Authorization: Bearer {token}

Get Dataset Info

GET https://api.powerbi.com/v1.0/myorg/groups/{workspace-id}/datasets/{dataset-id}

Get Refresh History

GET https://api.powerbi.com/v1.0/myorg/groups/{workspace-id}/datasets/{dataset-id}/refreshes

Troubleshooting

Power Query (M) Patterns Reference

Common patterns for .pq files in PBIP semantic models.

1. Folder-Based CSV Ingestion

The core pattern for combining multiple billing period CSVs:

let
    Source = Folder.Files(Parameter_ExportFolder),
    Filtered = Table.SelectRows(Source, each
        Text.Contains([Name], "UsageDetails") and Text.EndsWith([Name], ".csv")),
    ProcessFile = (fileContent as binary) =>
    let
        RawCSV = Csv.Document(fileContent, [Delimiter=",", QuoteStyle=QuoteStyle.Csv, Encoding=65001]),
        FirstCell = try RawCSV{0}{0} otherwise "",
        SkipRows = if Text.Contains(FirstCell, "Usage") or Text.Contains(FirstCell, "Report") then 2 else 0,
        Skipped = Table.Skip(RawCSV, SkipRows),
        PromotedHeaders = Table.PromoteHeaders(Skipped, [PromoteAllScalars=true])
    in
        PromotedHeaders,
    Combined = Table.Combine(
        Table.AddColumn(Filtered, "Data", each ProcessFile([Content]))[Data]
    )
in
    Combined

Key points:

• Folder.Files() discovers all files in the folder
• Text.Contains + Text.EndsWith filters by naming convention
• Inner function handles per-file processing (header skipping, encoding)
• Encoding=65001 = UTF-8
• Table.Combine merges all processed files

2. Column Renaming for Multiple CSV Versions

EA exports change column names across versions. Handle with List.Accumulate:

ColumnMapping = {
    {"Date", "UsageDate"},
    {"ExtendedCost", "PretaxCost"},
    {"PreTaxCost", "PretaxCost"},
    {"Cost", "PretaxCost"},
    {"MeterRegion", "ResourceLocation"}
},

RenameExisting = List.Accumulate(
    ColumnMapping,
    PreviousStep,
    (state, pair) =>
        if List.Contains(Table.ColumnNames(state), pair{0}) and pair{0} <> pair{1}
        then Table.RenameColumns(state, {{pair{0}, pair{1}}})
        else state
)

Only renames columns that actually exist — safe for mixed-version CSVs.

3. Type Conversions

Always explicit, never rely on auto-detection:

TypedTable = Table.TransformColumnTypes(RenameExisting, {
    {"UsageDate", type date},
    {"ConsumedQuantity", type number},
    {"ResourceRate", type number},
    {"PretaxCost", type number}
})

Adding a DateKey Column

AddDateKey = Table.AddColumn(TypedTable, "DateKey", each [UsageDate], type date)

4. Dimension Derivation from Fact Table

Extract unique dimension values from a fact table:

let
    Source = Fact_Usage,
    Distinct = Table.Distinct(
        Table.SelectColumns(Source, {"SubscriptionGuid", "SubscriptionName", "AccountName", "DepartmentName"})
    )
in
    Distinct

For dimensions with classification logic:

let
    Source = Fact_Usage,
    Distinct = Table.Distinct(
        Table.SelectColumns(Source, {"MeterCategory", "MeterSubCategory"})
    ),
    AddTier = Table.AddColumn(Distinct, "ServiceTier", each
        if Text.Contains([MeterCategory], "Virtual Machines") then "Compute"
        else if Text.Contains([MeterCategory], "Storage") then "Storage"
        else if Text.Contains([MeterCategory], "SQL") or Text.Contains([MeterCategory], "Cosmos") then "Database"
        else "Outros",
        type text)
in
    AddTier

5. JSON Tag Parsing

Parse Azure resource tags from a JSON column:

ParseTag = (tagsJson as nullable text, tagName as text) as nullable text =>
let
    Result = if tagsJson = null or tagsJson = "" then null
             else try Json.Document(tagsJson){[Key=tagName]}[Value]
                  otherwise
                      try Record.Field(Json.Document(tagsJson), tagName)
                      otherwise null
in
    Result,

AddExecutivo = Table.AddColumn(Source, "Executivo", each
    let tag = ParseTag([Tags], "Executivo")
    in if tag = null then "(nao atribuido)" else tag, type text),

AddTagCompliance = Table.AddColumn(Previous, "TagsCompletas", each
    [TemTagExecutivo] and [TemTagGestor] and [TemTagRotulo] and [TemTagAgrupamento] and [TemTagAmbiente],
    type logical)

The ParseTag function handles two JSON formats:

• Array of objects: [{"Key":"Executivo","Value":"Allan Lima"}]
• Simple record: {"Executivo":"Allan Lima"}

6. Calendar Table Generation (pt-BR)

let
    StartDate = #date(2024, 1, 1),
    EndDate = #date(2027, 12, 31),
    DayCount = Duration.Days(EndDate - StartDate) + 1,
    DateList = List.Dates(StartDate, DayCount, #duration(1, 0, 0, 0)),
    DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    Typed = Table.TransformColumnTypes(DateTable, {{"Date", type date}}),

    AddAno = Table.AddColumn(Typed, "Ano", each Date.Year([Date]), Int64.Type),
    AddMes = Table.AddColumn(AddAno, "Mes", each Date.Month([Date]), Int64.Type),

    MonthNames = {"Janeiro","Fevereiro","Marco","Abril","Maio","Junho",
                  "Julho","Agosto","Setembro","Outubro","Novembro","Dezembro"},
    AddNomeMes = Table.AddColumn(AddMes, "NomeMes", each MonthNames{[Mes]-1}, type text),

    MonthAbbrev = {"Jan","Fev","Mar","Abr","Mai","Jun","Jul","Ago","Set","Out","Nov","Dez"},
    AddAbrev = Table.AddColumn(AddNomeMes, "NomeMesAbrev", each MonthAbbrev{[Mes]-1}, type text),

    AddAnoMes = Table.AddColumn(AddAbrev, "AnoMes", each
        Text.From([Ano]) & Text.PadStart(Text.From([Mes]), 2, "0"), type text),
    AddLabel = Table.AddColumn(AddAnoMes, "AnoMesLabel", each
        [NomeMesAbrev] & "/" & Text.From([Ano]), type text),

    AddTrimestre = Table.AddColumn(AddLabel, "Trimestre", each
        "T" & Text.From(Number.RoundUp([Mes] / 3)), type text),

    DayNames = {"Segunda","Terca","Quarta","Quinta","Sexta","Sabado","Domingo"},
    AddDiaSemana = Table.AddColumn(AddTrimestre, "DiaSemana", each
        DayNames{Date.DayOfWeek([Date], Day.Monday)}, type text),

    AddEhDiaUtil = Table.AddColumn(AddDiaSemana, "EhDiaUtil", each
        Date.DayOfWeek([Date], Day.Monday) < 5, type logical)
in
    AddEhDiaUtil

7. Key-Value CSV Parsing (EA Balance)

EA Balance exports use a key-value format, not tabular:

let
    Source = Folder.Files(Parameter_ExportFolder),
    Filtered = Table.SelectRows(Source, each
        Text.Contains([Name], "ArmBalances") and Text.EndsWith([Name], ".csv")),

    ProcessBalance = (content as binary, fileName as text) =>
    let
        Lines = Lines.FromBinary(content, null, null, 65001),
        // Extract billing period from filename: ArmBalances_53329720_YYYYMM_en.csv
        Period = Text.BetweenDelimiters(fileName, "_", "_", 1),
        // Parse key-value lines: "Key,Currency,Value"
        ParseLine = (line as text) =>
            let parts = Text.Split(line, ",")
            in if List.Count(parts) >= 3 then {parts{0}, parts{2}} else null,
        Parsed = List.Select(List.Transform(Lines, ParseLine), each _ <> null),
        AsRecord = Record.FromList(
            List.Transform(Parsed, each _{1}),
            List.Transform(Parsed, each _{0})
        )
    in
        Record.AddField(AsRecord, "BillingPeriod", Period)
in
    ...

8. Parameter Usage

Create a configurable parameter in a .pq file:

"C:\data\billing-exports" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

Reference in other queries:

let
    FolderPath = Parameter_ExportFolder,
    Source = Folder.Files(FolderPath),
    ...

9. Null Handling Patterns

// Replace null ResourceGroup
CleanRG = Table.TransformColumns(Previous, {
    {"ResourceGroup", each if _ = null or _ = "" then "(sem grupo)" else _, type text}
}),

// Default for missing tags
AddTag = Table.AddColumn(Previous, "Executivo", each
    let tag = ParseTag([Tags], "Executivo")
    in if tag = null then "(nao atribuido)" else tag, type text),

// Safe number conversion (null → 0)
SafeNumber = Table.TransformColumns(Previous, {
    {"PretaxCost", each if _ = null then 0 else _, type number}
})

Tips

• Always specify Encoding=65001 (UTF-8) for CSV parsing
• Use try ... otherwise for resilient parsing
• Filter files early (Table.SelectRows on file names) before processing content
• Test each step individually in Power Query Editor before combining
• Use Table.Buffer() for tables referenced multiple times (performance)

Star Schema Guide for Power BI

Why Star Schema

Power BI's DAX engine is optimized for star schemas:

• Performance: Columnar storage + star schema = fast aggregations
• DAX simplicity: Time intelligence, CALCULATE filters, and USERELATIONSHIP all assume M:1 relationships
• Slicer behavior: One-direction cross-filtering from dimension to fact works naturally
• Maintainability: Clear separation of "what happened" (facts) vs "how to slice it" (dimensions)

Fact Table Design

Facts store measurements — things you count, sum, or average.

Grain Definition

Define the finest level of detail before designing:

• Fact_Usage: "One row per meter per resource per day"
• Fact_Marketplace: "One row per marketplace meter per day"
• Fact_Balance: "One row per billing period (monthly)"

Required Elements

• Foreign keys to each dimension (DateKey, SubscriptionGuid, ResourceGroup, etc.)
• Numeric columns for aggregation (PretaxCost, ConsumedQuantity)
• DateKey column for the Dim_Date relationship

Anti-patterns

• Don't store descriptive text in facts (put it in dimensions)
• Don't pre-aggregate (keep the finest grain)
• Don't duplicate dimension attributes in facts

Dimension Table Design

Dimensions store descriptive attributes for filtering and grouping.

Derivation Patterns

From fact table (most common):

Table.Distinct(Table.SelectColumns(Fact_Usage, {"SubscriptionGuid", "SubscriptionName"}))

Generated (calendar):

// Generate date range with locale-specific attributes
List.Dates(#date(2024,1,1), DayCount, #duration(1,0,0,0))

Manual (budget, targets):

#table(type table [Period=text, Amount=number], {{"202601", 1000000}})

Enriched (tags, classification):

// Derive from fact, then add parsed tags and compliance flags
Table.Distinct(...) → AddTag → AddCompliance

Key Decisions

• Use natural keys (SubscriptionGuid) when they're stable and unique
• Use surrogate keys (integer) when natural keys are long or composite
• Always have a "missing" row for orphaned fact records: "(sem grupo)", "(nao atribuido)"

Relationship Rules

                    ┌──────────────┐
                    │  Dim_Date    │
                    │  (Calendar)  │
                    └──────┬───────┘
                           │
        ┌──────────────────┼──────────────────┐
        │                  │                  │
┌───────┴────────┐  ┌──────┴────────┐  ┌──────┴──────────┐
│  Fact_Usage    │  │Fact_Marketplace│  │  Fact_Balance   │
│  (ACTIVE→Date) │  │ (INACTIVE)    │  │  (INACTIVE)     │
└───────┬────────┘  └──────┬────────┘  └─────────────────┘
        │                  │
   ┌────┴────┐        ┌───┴────┐
   │         │        │        │
┌──┴─────┐┌──┴────┐┌──┴──────┐│
│Dim_Sub ││Dim_RG ││Dim_Pub  ││
│        ││       ││lisher   ││
└────────┘└───┬───┘└─────────┘│
              │               │
         ┌────┴────┐          │
         │Dim_Svc  │          │
         └─────────┘     ┌────┴─────┐
                         │Dim_Budget│
                         └──────────┘

Rule 1: All M:1 from Fact to Dimension

// CORRECT
relationship Fact_Usage_to_Dim_Date
    fromColumn: Fact_Usage.DateKey       // Many side
    toColumn: Dim_Date.Date              // One side
    crossFilteringBehavior: oneDirection

Rule 2: One Active Relationship Per Path

When multiple fact tables share Dim_Date:

• Fact_Usage → Dim_Date: ACTIVE (most queried)
• Fact_Marketplace → Dim_Date: INACTIVE (isActive: false)
• Fact_Balance → Dim_Date: INACTIVE
• Dim_Budget → Dim_Date: INACTIVE

Rule 3: USERELATIONSHIP for Inactive

Every measure touching an inactive relationship must wrap in CALCULATE:

measure 'Custo Marketplace' =
    CALCULATE(
        SUM(Fact_Marketplace[PretaxCost]),
        USERELATIONSHIP(Fact_Marketplace[DateKey], Dim_Date[Date])
    )

Rule 4: One-Direction Cross-Filtering

Always crossFilteringBehavior: oneDirection. Bidirectional cross-filtering:

• Causes ambiguous filter propagation
• Can produce incorrect totals
• Breaks DAX expectations

Multiple Fact Tables

When facts share dimensions:

1. Create the dimension once (derive from the primary fact) 2. Add FK columns to all fact tables that reference it 3. Make the primary fact's relationship active, others inactive 4. Write USERELATIONSHIP measures for inactive paths

Shared dimensions in a FinOps model:

• Dim_Date: shared by Fact_Usage (active), Fact_Marketplace, Fact_Balance, Dim_Budget (all inactive)
• Dim_Subscription: shared by Fact_Usage (active), Fact_Marketplace (inactive)
• Dim_ResourceGroup: shared by Fact_Usage (active), Fact_Marketplace (inactive)

Calendar Table (Dim_Date)

Every model needs one. Requirements:

• Continuous dates (no gaps) covering the full data range
• isKey: true on the Date column
• Sort columns: AnoMes (YYYYMM) for proper month ordering
• Localized: month names, day names, quarter labels in the project language

Anti-Patterns

TMDL Syntax Reference

Tabular Model Definition Language — the text-based format for PBIP semantic models.

File Structure

definition/
├── model.tmdl              # Model-level settings (culture, version)
├── tables/                 # One .tmdl per table
│   ├── _Measures.tmdl      # Dedicated measures table
│   ├── Fact_Usage.tmdl     # Fact table definitions
│   ├── Dim_Date.tmdl       # Dimension table definitions
│   └── ...
├── expressions/            # One .pq per Power Query expression
│   ├── Fact_Usage.pq
│   ├── Dim_Date.pq
│   └── ...
└── relationships.tmdl      # All relationships in one file

Model Definition (model.tmdl)

model Model
    culture: pt-BR
    defaultPowerBIDataSourceVersion: powerBI_V3
    sourceQueryCulture: pt-BR

• culture: Locale for formatting (pt-BR, en-US, etc.)
• defaultPowerBIDataSourceVersion: Always powerBI_V3 for modern models
• sourceQueryCulture: Affects how PQ interprets literals

Table Definition

table Fact_Usage
    lineageTag: t-fact-usage

    column UsageDate
        dataType: dateTime
        lineageTag: col-usage-date
        summarizeBy: none
        sourceColumn: UsageDate

    column PretaxCost
        dataType: double
        lineageTag: col-pretax-cost
        summarizeBy: sum
        sourceColumn: PretaxCost
        formatString: R$ #,##0.00

    column ResourceGroup
        dataType: string
        lineageTag: col-rg
        summarizeBy: none
        sourceColumn: ResourceGroup

    column TagsCompletas
        dataType: boolean
        lineageTag: col-tags-completas
        summarizeBy: none
        sourceColumn: TagsCompletas

    partition Fact_Usage = m
        mode: import
        source =
            let Source = Expression.Evaluate(#"Fact_Usage", #shared) in Source

Column Properties

dataType Values

Measure Definition

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

Multi-line DAX Measure

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
    lineageTag: m-variacao-mom

Measure with Annotation

measure 'Contagem Reservas' =
    BLANK()
    formatString: 0
    displayFolder: Reservas
    lineageTag: m-contagem-reservas
    annotation note = "Placeholder - requires Reservation Details export data"

Measure Properties

Calculated Table Pattern (_Measures)

The dedicated measures table pattern:

table _Measures
    lineageTag: measures-001

    // All measures go here organized by displayFolder sections

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

    // Hidden column required for calculated table
    column MeasureColumn
        dataType: int64
        isHidden: true
        lineageTag: measures-col
        summarizeBy: none
        sourceColumn: none

    partition _Measures = calculated
        source = ROW("MeasureColumn", 0)

Key points:

• partition = calculated with source = ROW(...) makes it a calculated table
• Hidden column exists only to satisfy the table requirement
• sourceColumn: none for calculated columns

Relationship Definition

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

Inactive Relationship

relationship Fact_Marketplace_to_Dim_Date
    fromColumn: Fact_Marketplace.DateKey
    toColumn: Dim_Date.Date
    isActive: false
    crossFilteringBehavior: oneDirection

Relationship Properties

Partition Types

Import from PQ Expression

partition Fact_Usage = m
    mode: import
    source =
        let Source = Expression.Evaluate(#"Fact_Usage", #shared) in Source

Calculated Table

partition _Measures = calculated
    source = ROW("MeasureColumn", 0)

lineageTag Convention

Rules:

• Kebab-case (lowercase with hyphens)
• Must be unique across the entire model
• Descriptive but concise
• Portuguese abbreviations OK: m-conf-executivo, m-variacao-mom

Format Strings

Workflow: Add DAX Measure

Add a new DAX measure to the _Measures table.

Step 1: Identify the Measure Category

Choose a displayFolder:

Step 2: Write the Measure

Add to tables/_Measures.tmdl under the appropriate section.

Template

measure 'Measure Name' =
    <DAX expression>
    formatString: <format>
    displayFolder: <folder>
    lineageTag: m-measure-name-kebab

Common Patterns

Simple aggregation:

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

With USERELATIONSHIP (for inactive relationships):

measure 'Marketplace Cost' =
    CALCULATE(
        SUM(Fact_Marketplace[PretaxCost]),
        USERELATIONSHIP(Fact_Marketplace[DateKey], Dim_Date[Date])
    )
    formatString: R$ #,##0.00
    displayFolder: Custo
    lineageTag: m-marketplace-cost

Safe percentage with VAR/RETURN:

measure 'Variance %' =
    VAR Current = [Current Measure]
    VAR Previous = [Previous Measure]
    RETURN
        IF(
            Previous <> 0,
            DIVIDE(Current - Previous, Previous),
            BLANK()
        )
    formatString: 0.0%;-0.0%;0.0%
    displayFolder: Variacao
    lineageTag: m-variance-pct

Time intelligence:

measure 'MTD Cost' =
    CALCULATE([Combined Cost], DATESMTD(Dim_Date[Date]))
    formatString: R$ #,##0.00
    displayFolder: Custo
    lineageTag: m-mtd-cost

Counting with filter:

measure 'Non-Compliant Count' =
    COUNTROWS(FILTER(Dim_ResourceGroup, Dim_ResourceGroup[TagsCompletas] = FALSE()))
    formatString: 0
    displayFolder: Conformidade Tags
    lineageTag: m-non-compliant-count

Placeholder (data not yet available):

measure 'RI Coverage %' =
    BLANK()
    formatString: 0.0%
    displayFolder: Reservas
    lineageTag: m-ri-coverage
    annotation note = "Placeholder - requires Reservation Details export data"

Step 3: Format String Reference

The three-part percentage format handles positive, negative, and zero values.

Step 4: lineageTag Convention

• Prefix: m- for all measures
• Kebab-case: m-custo-total, m-variacao-mom, m-conf-executivo
• Must be unique across the entire model
• Keep it descriptive but concise

Checklist

• [ ] Measure added to _Measures.tmdl
• [ ] Correct formatString for the data type
• [ ] displayFolder assigned
• [ ] Unique lineageTag with m- prefix
• [ ] Uses DIVIDE() instead of / for safe division
• [ ] Uses USERELATIONSHIP() if accessing inactive relationship
• [ ] VAR/RETURN pattern for multi-step calculations
• [ ] BLANK() returned for edge cases (not 0 or error)

Workflow: Add Table

Add a new fact or dimension table to the PBIP semantic model.

Decision: Fact vs Dimension

Step 1: Create the Power Query Expression

Create expressions/TableName.pq with the M expression.

For CSV-based fact tables — use Folder.Files pattern:

let
    Source = Folder.Files(Parameter_ExportFolder),
    Filtered = Table.SelectRows(Source, each
        Text.Contains([Name], "FilePattern") and Text.EndsWith([Name], ".csv")),
    Combined = Table.Combine(
        Table.AddColumn(Filtered, "Data", each
            let
                Raw = Csv.Document([Content], [Delimiter=",", Encoding=65001]),
                Headers = Table.PromoteHeaders(Raw, [PromoteAllScalars=true])
            in Headers
        )[Data]
    ),
    Typed = Table.TransformColumnTypes(Combined, {
        {"DateColumn", type date},
        {"AmountColumn", type number}
    }),
    AddDateKey = Table.AddColumn(Typed, "DateKey", each [DateColumn], type date)
in
    AddDateKey

For derived dimension tables — extract from fact:

let
    Source = FactTableName,
    Distinct = Table.Distinct(
        Table.SelectColumns(Source, {"KeyColumn", "DescColumn1", "DescColumn2"})
    )
in
    Distinct

For manually maintained dimensions (like Budget):

let
    Source = #table(
        type table [Period=text, Amount=number, Category=text],
        {
            {"202601", 1000000, "Total"},
            {"202602", 1200000, "Total"}
        }
    )
in
    Source

Step 2: Create the TMDL Definition

Create tables/TableName.tmdl:

table TableName
    lineageTag: t-table-name

    column ColumnName
        dataType: string
        lineageTag: col-column-name
        summarizeBy: none
        sourceColumn: ColumnName

    column AmountColumn
        dataType: double
        lineageTag: col-amount
        summarizeBy: sum
        sourceColumn: AmountColumn
        formatString: R$ #,##0.00

    column DateKey
        dataType: dateTime
        lineageTag: col-datekey
        summarizeBy: none
        sourceColumn: DateKey
        isKey: true

    partition TableName = m
        mode: import
        source =
            let
                Source = Expression.Evaluate(
                    #"TableName",
                    #shared
                )
            in
                Source

Column dataType Reference

Step 3: Add Relationships

Append to relationships.tmdl:

relationship TableName_to_DimName
    fromColumn: TableName.ForeignKey
    toColumn: DimName.PrimaryKey
    crossFilteringBehavior: oneDirection

If the dimension already has an active relationship from another fact table, add isActive: false and use USERELATIONSHIP() in DAX measures.

Step 4: Verify

1. Open .pbip in Power BI Desktop 2. Refresh data 3. Check table appears in model view 4. Verify relationship lines in diagram 5. Test a simple measure against the new table

Checklist

• [ ] .pq file created in expressions/
• [ ] .tmdl file created in tables/ with all columns
• [ ] Every column has lineageTag, dataType, summarizeBy
• [ ] DateKey column added for date relationship
• [ ] Relationship added to relationships.tmdl
• [ ] Null handling in PQ (defaults for empty values)
• [ ] Type conversions explicit in PQ

Workflow: Connector Authentication

Troubleshoot and configure Power BI data source connectors, especially Azure Cost Management for EA enrollments.

Azure Cost Management Connector (EA)

The built-in Power BI connector for Azure Cost Management has different permission requirements depending on the billing account type.

EA Enrollment Connector

Required Role: Enterprise Administrator (read-only) at the billing account level.

This is the most common gotcha. Standard Azure RBAC roles do NOT work:

Why RBAC Doesn't Work

The EA connector uses OAuth2 against the EA Billing layer, which is a completely separate permission system from Azure RBAC. Granting Cost Management Reader at any scope does nothing for the EA connector.

Granting EA Admin (Read-Only)

1. Sign in to https://ea.azure.com as an existing EA Admin 2. Navigate to Manage > Enrollment tab 3. Click + Add Administrator 4. Enter the user's email (must be in the Entra ID tenant) 5. Select Read Only checkbox 6. Click Add 7. Wait up to 30 minutes for propagation

Connecting in Power BI Desktop

1. Get Data > Azure > Azure Cost Management 2. Choose scope: Enrollment Number 3. Enter enrollment ID (e.g., 53329720) 4. Click Connect 5. Sign in with the account that has EA Admin role 6. For guest accounts: use "Sign into an organization" flow and enter the host tenant FQDN

Common Failures

EA Policy Prerequisites

Both of these must be enabled in the EA portal (Billing Account > Policies):

1. DA view charges — Department Administrators can see costs 2. AO view charges — Account Owners can see costs

If these are disabled, even EA Admins may see limited data.

Alternative: Export-Based Approach

If EA Admin role cannot be granted (common in large enterprises with strict governance):

1. Use Cost Management Exports to Azure Blob Storage

• Requires Cost Management Contributor (Azure RBAC) + storage access
• Configure in Azure Portal > Cost Management > Exports

2. In Power BI, use Azure Blob Storage connector instead

• Get Data > Azure > Azure Blob Storage
• Enter storage account URL and key/SAS token

3. Power Query reads the exported CSVs from blob

This avoids the EA billing layer entirely and uses standard Azure RBAC.

Other Connectors

Azure Blob Storage

• Needs: Storage account URL + account key or SAS token
• Or: Entra ID account with Storage Blob Data Reader role

SQL Database

• Needs: Server name, database name
• Auth: SQL auth (username/password) or Entra ID
• For Service: may need gateway if private endpoint

SharePoint / OneDrive

• Needs: Site URL
• Auth: Organizational account
• Common issue: URL must be the SharePoint site URL, not the file URL

Token Cache Management

When credentials change (role granted, password reset, etc.):

1. Power BI Desktop: File > Options > Data Source Settings > Clear Permissions 2. Power BI Service: Dataset Settings > Data source credentials > Edit credentials 3. Browser: Clear cookies for login.microsoftonline.com

Workflow: Data Modeling (Star Schema)

Design and implement a star schema data model in PBIP.

Step 1: Identify Fact Tables

Each fact table represents a business process or measurement:

Grain: Define the finest level of detail. E.g., "one row per meter per resource per day."

Step 2: Identify Dimensions

For each fact table, identify the axes of analysis:

Step 3: Define Relationships

All relationships must be Many-to-One (M:1) from fact to dimension.

Rules

1. One-direction cross-filtering — always crossFilteringBehavior: oneDirection 2. One active relationship per path — if two fact tables share Dim_Date, only one can be active. The other uses isActive: false. 3. USERELATIONSHIP in DAX — measures that need inactive relationships use CALCULATE(..., USERELATIONSHIP(Fact.FK, Dim.PK))

Active vs Inactive Decision

When multiple fact tables share a dimension:

Fact_Usage → Dim_Date     (ACTIVE)
Fact_Marketplace → Dim_Date (INACTIVE — use USERELATIONSHIP)
Fact_Balance → Dim_Date    (INACTIVE — use USERELATIONSHIP)
Dim_Budget → Dim_Date      (INACTIVE — use USERELATIONSHIP)

The most frequently queried fact table gets the active relationship.

Step 4: Write relationships.tmdl

// Star Schema Relationships

// === Fact_Usage (active relationships) ===
relationship Fact_Usage_to_Dim_Date
    fromColumn: Fact_Usage.DateKey
    toColumn: Dim_Date.Date
    crossFilteringBehavior: oneDirection

relationship Fact_Usage_to_Dim_Subscription
    fromColumn: Fact_Usage.SubscriptionGuid
    toColumn: Dim_Subscription.SubscriptionGuid
    crossFilteringBehavior: oneDirection

// === Fact_Marketplace (inactive — use USERELATIONSHIP) ===
relationship Fact_Marketplace_to_Dim_Date
    fromColumn: Fact_Marketplace.DateKey
    toColumn: Dim_Date.Date
    isActive: false
    crossFilteringBehavior: oneDirection

Step 5: Shared Dimensions

When the same dimension serves multiple fact tables:

1. Create the dimension once (derived from the primary fact table) 2. Ensure the FK column exists in all fact tables that reference it 3. Make the PQ expression source the primary fact for derivation 4. Add relationships from each fact table to the shared dimension

Step 6: Calendar Table (Dim_Date)

Every model needs a calendar table. Generate it in PQ covering the full date range:

• Start: earliest date in data (or a fixed start like 2024-01-01)
• End: latest date in data + 1 year
• Include: Year, Month, MonthName, Quarter, DayOfWeek, IsBusinessDay
• Localize: Portuguese month/day names for Brazilian projects

Mark the Date column as isKey: true in the TMDL.

Validation Checklist

• [ ] All relationships are M:1 (from fact to dimension)
• [ ] Cross-filtering is one-direction everywhere
• [ ] Only one active relationship per dimension-to-fact path
• [ ] Inactive relationships have corresponding USERELATIONSHIP measures
• [ ] Every fact table has a DateKey → Dim_Date relationship
• [ ] Dim_Date covers the full date range of all fact tables
• [ ] No circular relationships
• [ ] No bidirectional cross-filtering
• [ ] No snowflake chains (dimension → dimension)

Workflow: New PBIP Project

Scaffold a new Power BI Project (PBIP) from scratch with git-friendly structure.

Steps

1. Create Directory Structure

ProjectName/
├── ProjectName.pbip
├── ProjectName.Report/
│   ├── report.json
│   ├── definition.pbir
│   └── StaticResources/SharedResources/BaseThemes/
├── ProjectName.SemanticModel/
│   ├── definition.pbism
│   └── definition/
│       ├── model.tmdl
│       ├── tables/
│       ├── expressions/
│       └── relationships.tmdl
├── scripts/
└── docs/

2. Create .pbip File

{
  "version": "1.0",
  "artifacts": [
    {
      "report": {
        "path": "ProjectName.Report"
      }
    },
    {
      "dataset": {
        "path": "ProjectName.SemanticModel"
      }
    }
  ]
}

3. Create definition.pbism

{
  "version": "1.0",
  "settings": {}
}

4. Create definition.pbir

{
  "version": "1.0",
  "datasetReference": {
    "byPath": {
      "path": "../ProjectName.SemanticModel"
    }
  }
}

5. Create model.tmdl

model Model
    culture: pt-BR
    defaultPowerBIDataSourceVersion: powerBI_V3
    sourceQueryCulture: pt-BR

Adjust culture to match the project locale.

6. Create _Measures Table

In tables/_Measures.tmdl:

table _Measures
    lineageTag: measures-001

    column MeasureColumn
        dataType: int64
        isHidden: true
        lineageTag: measures-col
        summarizeBy: none
        sourceColumn: none

    partition _Measures = calculated
        source = ROW("MeasureColumn", 0)

7. Create Parameter Expression

In expressions/Parameter_ExportFolder.pq (if using folder-based ingestion):

"C:\data\exports" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

8. Create relationships.tmdl

Start with a comment header:

// Star Schema Relationships - All Many-to-One from Facts to Dimensions

Add relationships as tables are created.

9. Create Theme (Optional)

In StaticResources/SharedResources/BaseThemes/CustomTheme.json:

{
  "name": "CustomTheme",
  "dataColors": ["#003366", "#0066CC", "#FF6600", "#339966", "#CC3333", "#9933CC"],
  "background": "#FFFFFF",
  "foreground": "#333333",
  "tableAccent": "#0066CC"
}

10. Initialize Git

git init
# Add .gitignore for PBI artifacts
echo "*.pbit" >> .gitignore
echo ".pbi/" >> .gitignore
echo "*.pbix" >> .gitignore

Checklist

• [ ] .pbip opens in Power BI Desktop
• [ ] model.tmdl has correct culture setting
• [ ] _Measures table exists with hidden column
• [ ] Parameter configured for data source
• [ ] relationships.tmdl created (even if empty)
• [ ] Git initialized with .gitignore

Workflow: Publish & Scheduled Refresh

Publish a PBIP report to Power BI Service and configure automated refresh.

Step 1: Publish from Desktop

1. Open the .pbip in Power BI Desktop 2. Verify data is refreshed and report looks correct 3. Home > Publish 4. Select destination workspace 5. If replacing existing, confirm overwrite

Step 2: Configure Data Gateway (for local/file data sources)

If the data source is local files (CSV folder), a network share, or on-prem database, you need the On-premises Data Gateway.

Install Gateway

1. Download from https://powerbi.microsoft.com/gateway/ 2. Install on a server that:

• Has access to the data source (CSV folder, file share, database)
• Is always on (not a laptop)
• Has reliable internet connection

3. Sign in with Power BI account during setup 4. Register the gateway in the Power BI Service tenant

Add Data Source to Gateway

1. Power BI Service > Settings (gear icon) > Manage gateways 2. Select your gateway > Add data source 3. For folder-based CSV:

• Data source type: Folder
• Path: the full path to the CSV folder (as seen from the gateway machine)

4. Configure credentials (Windows auth or organizational account)

Step 3: Configure Dataset in Service

1. Go to the workspace in Power BI Service 2. Find the dataset (semantic model) > Settings (gear icon) 3. Under Gateway connection:

• Map each data source to the gateway data source
• The folder parameter must match the gateway data source path

4. Under Data source credentials:

• Verify each source has valid credentials
• For Azure Cost Management connector: use organizational account with EA Admin role

5. Under Parameters:

• Verify Parameter_ExportFolder value matches the gateway machine path

Step 4: Schedule Refresh

1. In dataset settings > Scheduled refresh 2. Toggle Keep your data up to date to ON 3. Set refresh frequency:

• Daily at 08:00 (typical for FinOps — after overnight export scripts run)
• Or multiple times per day if data changes frequently

4. Set timezone 5. Enable Send refresh failure notification to dataset owner 6. Apply

Step 5: Verify First Refresh

1. Trigger a manual refresh: dataset > Refresh now 2. Check Refresh history for success/failure 3. If failed, check error details:

• Gateway connectivity issues
• Credential problems
• Data source path mismatches
• Query timeout

Alternative: Direct API Connection (No Gateway)

If using the Azure Cost Management connector or other cloud connectors:

1. No gateway needed — PBI Service connects directly 2. In dataset settings, configure Data source credentials with OAuth2 3. The account must have appropriate Azure roles (EA Admin for EA connector) 4. Schedule refresh as above

Refresh Troubleshooting

Automation with Python

Trigger refresh programmatically via Power BI REST API:

# Trigger refresh
curl -X POST \
  "https://api.powerbi.com/v1.0/myorg/groups/{workspace-id}/datasets/{dataset-id}/refreshes" \
  -H "Authorization: Bearer {access-token}" \
  -H "Content-Type: application/json"

Or use the export_billing_data.py script to export fresh CSVs, then let the scheduled refresh pick them up.

Workflow: Troubleshooting

Common Power BI issues and their solutions.

Data Loading Issues

TMDL Errors

DAX Errors

Power Query Debugging

Check PQ Output Before TMDL

1. Open .pbip in Power BI Desktop 2. Transform Data (Power Query Editor) 3. Click on the table name 4. Check Applied Steps — click each step to see intermediate results 5. Verify column names, types, and row counts

Common PQ Patterns That Break

// BAD: This silently returns empty if no files match
Table.SelectRows(Source, each Text.Contains([Name], "ExactWrongName"))

// GOOD: Add error handling or log
Table.SelectRows(Source, each
    Text.Contains([Name], "UsageDetails", Comparer.OrdinalIgnoreCase))

Encoding Issues

• EA portal exports are UTF-8 with BOM
• Python exports should use encoding="utf-8"
• In PQ: Csv.Document(content, [Encoding=65001]) for UTF-8

Relationship Debugging

Symptom: Slicer Doesn't Filter Visuals

1. Check relationship exists in relationships.tmdl 2. Check cross-filtering direction (should be oneDirection from fact to dim) 3. Check if relationship is active (isActive: false means it won't auto-filter) 4. If inactive, the measure must use USERELATIONSHIP

Symptom: Numbers Don't Add Up

1. Check for many-to-many relationships (should be M:1) 2. Check for bidirectional cross-filtering (should be one-direction) 3. Check if measure uses wrong relationship path 4. Verify DateKey in fact matches Date in Dim_Date (type and format)

Git / PBIP Issues

Performance