Automate Power BI · BI Developer Toolkit

Before you automate anything

Use a service principal for anything scheduled. A script that runs as you breaks when you go on holiday or leave. Register an app in Microsoft Entra ID, put it in a security group, and allow that group in the Fabric admin portal's developer settings (Service principals can call Fabric public APIs; admin APIs have their own read-only admin API setting).
Give it the least access that works: workspace Member or Contributor only where it deploys or refreshes; admin APIs only for an inventory principal.
Keep secrets out of code and out of Git. Read them from environment variables, a pipeline secret or Azure Key Vault. The samples below use placeholders like <tenant-id> and environment variables; nothing in them is a real credential.
Respect limits. Back off on HTTP 429 and honour Retry-After; the scanner API, for example, allows a limited number of requests per hour.

The automation track has hands-on assignments with mock responses, so you can practise without a tenant.

Authentication

API	Base URL	Token scope
Power BI REST	`https://api.powerbi.com/v1.0/myorg`	`https://analysis.windows.net/powerbi/api/.default`
Fabric REST	`https://api.fabric.microsoft.com/v1`	`https://api.fabric.microsoft.com/.default`

Python (MSAL, client credentials)

python

# pip install msal requests
import os, msal, requests

TENANT = os.environ["PBI_TENANT_ID"]          # never hard-code these
CLIENT = os.environ["PBI_CLIENT_ID"]
SECRET = os.environ["PBI_CLIENT_SECRET"]

app = msal.ConfidentialClientApplication(CLIENT, authority=f"https://login.microsoftonline.com/{TENANT}", client_credential=SECRET)

def token(scope="https://analysis.windows.net/powerbi/api/.default"):
    result = app.acquire_token_for_client(scopes=[scope])
    if "access_token" not in result:
        raise RuntimeError(result.get("error_description"))
    return result["access_token"]

def pbi(method, path, **kw):
    r = requests.request(method, "https://api.powerbi.com/v1.0/myorg" + path,
                         headers={"Authorization": f"Bearer {token()}"}, timeout=60, **kw)
    r.raise_for_status()
    return r.json() if r.content else None

PowerShell (MicrosoftPowerBIMgmt)

powershell

# Install-Module MicrosoftPowerBIMgmt -Scope CurrentUser
$cred = New-Object PSCredential($env:PBI_CLIENT_ID, (ConvertTo-SecureString $env:PBI_CLIENT_SECRET -AsPlainText -Force))
Connect-PowerBIServiceAccount -ServicePrincipal -Credential $cred -Tenant $env:PBI_TENANT_ID

# any REST call, with the module handling the token
Invoke-PowerBIRestMethod -Url "groups" -Method Get | ConvertFrom-Json

List workspaces, reports and semantic models

python

for ws in pbi("GET", "/groups")["value"]:
    reports = pbi("GET", f"/groups/{ws['id']}/reports")["value"]
    models  = pbi("GET", f"/groups/{ws['id']}/datasets")["value"]
    print(ws["name"], len(reports), "reports,", len(models), "semantic models")

/groups returns the workspaces the identity is a member of. For every workspace in the tenant, use the admin APIs (next sections).

Trigger a refresh and check its status

python

ws, model = "<workspace-id>", "<semantic-model-id>"

# service principals must use NoNotification
pbi("POST", f"/groups/{ws}/datasets/{model}/refreshes", json={"notifyOption": "NoNotification"})

last = pbi("GET", f"/groups/{ws}/datasets/{model}/refreshes?$top=1")["value"][0]
print(last["status"], last.get("startTime"), last.get("endTime"))
# status: Unknown (still running), Completed, Failed or Disabled; read serviceExceptionJson on failure

For large models, enhanced refresh (same endpoint, with a body like {"type": "Full", "objects": [{"table": "FactSales"}], "commitMode": "transactional"}) refreshes chosen tables or partitions and can be cancelled. Poll the refresh by its ID rather than in a tight loop: once a minute is plenty.

Inventory the tenant and extract lineage

The admin scanner APIs return workspaces with their items, users, data sources, lineage and (if metadata scanning is enabled) tables, columns, measures and expressions.

python

import time
modified = pbi("GET", "/admin/workspaces/modified?excludePersonalWorkspaces=True")
ids = [w["id"] for w in modified]

results = []
for i in range(0, len(ids), 100):                      # up to 100 workspaces per scan
    scan = pbi("POST", "/admin/workspaces/getInfo?lineage=True&datasourceDetails=True&datasetSchema=True&datasetExpressions=True",
               json={"workspaces": ids[i:i + 100]})
    while pbi("GET", f"/admin/workspaces/scanStatus/{scan['id']}")["status"] != "Succeeded":
        time.sleep(10)
    results.append(pbi("GET", f"/admin/workspaces/scanResult/{scan['id']}"))

Save the results as JSON or load them into a Lakehouse, and you have the raw material for the inventory in Sprint 10's architecture review: owners, endorsement, data sources, duplicated measures and refresh health.

Find stale content

Combine the inventory with the activity log: reports nobody opened in 90 days are retirement candidates (not deletion candidates; see report retirement).

python

from datetime import date, timedelta
viewed = set()
for d in range(30):                                     # one UTC day per call
    day = date.today() - timedelta(days=d + 1)
    url = f"/admin/activityevents?startDateTime='{day}T00:00:00Z'&endDateTime='{day}T23:59:59Z'&$filter=Activity eq 'viewreport'"
    page = pbi("GET", url)
    while True:
        viewed.update(e.get("ReportId") for e in page.get("activityEventEntities", []))
        if page.get("lastResultSet"):
            break
        page = requests.get(page["continuationUri"], headers={"Authorization": f"Bearer {token()}"}).json()

Keep your own history: the activity log only goes back a limited number of days, so export it daily if you want trends.

List gateways and their data sources

python

for gw in pbi("GET", "/gateways")["value"]:
    sources = pbi("GET", f"/gateways/{gw['id']}/datasources")["value"]
    print(gw["name"], [s["datasourceType"] for s in sources])

The identity sees only gateways it administers.

Export model metadata

Fabric REST: POST /v1/workspaces/{workspaceId}/semanticModels/{modelId}/getDefinition returns the model definition (TMDL parts) as a long-running operation.
XMLA: in SSMS or with TOM, script the database as TMSL.
PBIP in Git: if the model already lives in Git, the repository is the metadata. This is the best option.

TMSL and TOM through the XMLA endpoint

Refresh one table's partitions with TMSL (run in SSMS against powerbi://api.powerbi.com/v1.0/myorg/<workspace name>):

json

{
  "refresh": {
    "type": "full",
    "objects": [ { "database": "Sales Model", "table": "FactSales" } ]
  }
}

With TOM in PowerShell (the Analysis Services client libraries installed):

powershell

Add-Type -Path "Microsoft.AnalysisServices.Tabular.dll"
$server = New-Object Microsoft.AnalysisServices.Tabular.Server
$server.Connect("Data Source=powerbi://api.powerbi.com/v1.0/myorg/Sales;User ID=app:$($env:PBI_CLIENT_ID)@$($env:PBI_TENANT_ID);Password=$($env:PBI_CLIENT_SECRET)")
$model = $server.Databases.GetByName("Sales Model").Model
$model.Tables | ForEach-Object { "{0}: {1} measures" -f $_.Name, $_.Measures.Count }
$server.Disconnect()

Tabular Editor scripts

C# scripts in Tabular Editor automate model hygiene:

csharp

// Give every measure without a description a TODO, so BPA and reviewers catch it
foreach (var m in Model.AllMeasures.Where(m => string.IsNullOrWhiteSpace(m.Description)))
    m.Description = "TODO: describe " + m.Name;

// Hide every key column used in a relationship
foreach (var c in Model.AllColumns.Where(c => c.Name.EndsWith("Key") && c.UsedInRelationships.Any()))
    c.IsHidden = true;

Fabric CLI

The Fabric CLI (fab) navigates Fabric like a file system and wraps the REST APIs:

bash

pip install ms-fabric-cli
fab auth login
fab ls                         # workspaces
fab ls "Sales.Workspace"       # items in a workspace

Deploy PBIP

For deployment from Git to workspaces, use fabric-cicd (Python) and a GitHub Actions or Azure DevOps pipeline. The full walkthrough is in Ship Power BI like software.

Something missing or out of date? Open an issue or edit content/toolkit/guides/automate-power-bi.md.