Indeed, models of Power BI data can be updated by making use of either of the following approaches: Power BI REST APIs, Tabular Editor, XMLA endpoints, and third-party applications.
1. Automate using Power BI REST API:
Power BI allows for the automation of dataset updates through their REST APIs. These REST APIs include refreshing datasets, updating the schema of tables, or managing relationships. You can:
Push changes in the dataset by API requests.
Use the Update Datasources API to change the sources dynamically.
By API, a programmatic refresh of datasets is triggered.
Example API Call (PowerShell or Python):
POST https://api.powerbi.com/v1.0/myorg/groups/{workspaceId}/datasets/{datasetId}/refreshes
2. Working with Metadata Using Tabular Editor
Tabular Editor 2 (free) or Tabular Editor 3 (paid) allows for scripting changes of DAX measures, relationships, and columns. Some of the things you can do are:
Change the metadata of a model through C# scripts.
Automate changes through the Command Line Interface (CLI).
Deploy changes programmatically through ALM Toolkit or in Git.
Here's an example of using a C# script to add a new measure:
Model.Tables["Sales"].AddMeasure("TotalSales", "SUM(Sales[Amount])");
3. Using XMLA Endpoints for Direct Model Editing
For Power BI Premium or PPU, you can enable XMLA endpoints, which allow direct modification of tabular models using SSMS or PowerShell.
- Use TMSL (Tabular Model Scripting Language) to edit the model.
- Modify tables, relationships, and measures programmatically.
Example TMSL Script to add a column:
{
"create": {
"database": "MyDataset",
"table": {
"name": "Sales",
"columns": [
{
"name": "NewColumn",
"datatype": "string"
}
]
}
}
}
Please follow the best practices to maintain data model consistency:
Version control: Store model changes in Git to keep track of changes.
Automated testing: Validate measures and relationships using DAX studio.
Scheduled updates: Use Azure Data Factory or Power Automate to schedule changes.
