TMDL and version control for Power BI semantic models

A Power BI semantic model saved as TMDL is a folder of text files, one per table. Change a measure and Git shows the two lines that changed, in the file for that table. That is the whole reason to care: it turns “someone republished the model and the numbers moved” into a pull request a colleague can read before it ships.

This article covers what TMDL is, where it sits next to PBIP and TMSL, how to read the syntax, and what I look for when reviewing a semantic model change. The examples come from my RTT waiting list semantic model, which I checked by loading it with Microsoft’s own TMDL serializer.

What TMDL is

Tabular Model Definition Language is Microsoft’s text format for tabular model metadata at compatibility level 1200 or higher. It covers the whole Tabular Object Model (TOM), so every table, column, measure, relationship, role and partition property that exists in TOM can be written in TMDL. It looks like YAML: objects are declared by type and name, and indentation shows what belongs to what.

Three formats get confused, so here they are side by side:

PBIP is the container; TMSL and TMDL are the two ways it can store the semantic model. The <name>.SemanticModel folder always has a definition.pbism file. It then holds either model.bim (TMSL) or a definition\ folder (TMDL). A definition.pbism version of 4.0 or above allows either. The single JSON file is where merge conflicts go to live; the folder is what you want in Git.

The folder

The default layout has one level of subfolders. Tables, roles, cultures and perspectives get a file each; everything else sits in a handful of root files. Here is the one from the RTT model:

RTT.SemanticModel/
├── definition.pbism
└── definition/
    ├── database.tmdl        compatibility level
    ├── model.tmdl           model properties, and the table order
    ├── expressions.tmdl     shared Power Query expressions and parameters
    ├── relationships.tmdl   every relationship in the model
    └── tables/
        ├── Date.tmdl        columns, measures, hierarchies and partitions
        ├── Pathways.tmdl
        └── ...

Everything that belongs to a table (its columns, measures, hierarchies and partitions) lives in that table’s file. A new measure on the Pathways table is a change to Pathways.tmdl, which keeps diffs small and conflicts rare. The RTT model has no roles or calculation groups; the theatre utilisation semantic model shows both in TMDL.

Reading the syntax

Tables and columns

/// Completed weeks waited, in bands. A band includes its lower edge and excludes its upper edge, so 18, 52, 65 and 78 weeks always fall on a boundary.
table 'Wait Band'

	column 'Wait Band Key'
		dataType: int64
		isHidden
		isKey
		formatString: 0
		summarizeBy: none
		sourceColumn: wait_band_key

	column 'Wait Band'
		dataType: string
		summarizeBy: none
		sourceColumn: wait_band
		sortByColumn: 'Wait Band Key'

The rules that matter for reading it:

• An object is its type followed by its name. Names need single quotes if they contain a dot, equals sign, colon, single quote or whitespace, and a single quote inside a name is doubled.
• Properties follow a colon and must fit on one line.
• A boolean property can be written on its own, like isHidden, which means true.
• References to other objects use the object’s name with the same quoting rules. sortByColumn: 'Wait Band Key' points at the hidden column above.
• A /// line directly above an object is its description, the text Power BI shows as a tooltip in the field list.
• Indentation is structure. Power BI Desktop and the serializer write one tab per level. Child objects sit one level in from their parent, and properties one level in from the object they belong to.

Measures and other expressions

Some objects have a default property that goes after an equals sign on the declaration line. For a measure it’s the DAX expression. A long expression starts on the next line, indented one level deeper than the measure’s properties:

	/// Incomplete pathways at the latest snapshot in the current date filter. Semi-additive: it never adds weeks together.
	measure 'Waiting List' =
			VAR AsAt = [Latest Snapshot Date]
			RETURN
				CALCULATE ( SUM ( 'Waiting List Snapshot'[Pathway Count] ), 'Date'[Date] = AsAt )
		formatString: #,0
		displayFolder: Waiting list

Everything at the deeper level is DAX; formatString and displayFolder, back at the property level, are not. The same rule covers Power Query partitions, calculated columns and calculation items. If an expression needs whitespace kept exactly, it can be wrapped in triple backticks. The serializer does this itself when an expression has trailing spaces or blank lines holding whitespace, which is one way a harmless-looking edit can produce an odd diff.

Relationships

All relationships live in relationships.tmdl:

relationship 4266e0c7-e6fe-5f23-805c-6d368d813243
	isActive: false
	fromColumn: Pathways.'Clock Stop Date'
	toColumn: Date.Date

fromColumn is the many side and toColumn the one side, written as Table.Column with the usual quoting. Anything left out takes its default: active, many-to-one, filtering in one direction. So a relationship that shows only the two column lines is the plain kind, and any extra line is a decision someone made. Power BI Desktop names relationships with GUIDs, so read the column lines, not the name.

Order and parameters

model.tmdl holds model-wide settings and ref lines that fix the order of tables, roles and cultures:

ref table Date
ref table Specialty
ref table 'Waiting List Snapshot'
ref table Pathways

A table file with no ref is still loaded and goes to the end of the list; a ref to a missing file is ignored. Parameters are shared expressions in expressions.tmdl, with the Power Query meta record that makes them parameters:

expression CsvFolder = "C:\RTT\data\" meta [IsParameterQuery = true, Type = "Text", IsParameterQueryRequired = true]

Reviewing a semantic model pull request

With TMDL, a model change reads like any other code change. These are the things I check, roughly in order of how often they matter.

Measure logic. This diff looks like a tidy-up:

 	/// Pathways waiting fewer than 18 completed weeks (under 126 days).
-	measure 'Within 18 Weeks' = CALCULATE ( [Waiting List], 'Wait Band'[Min Weeks] < 18 )
+	measure 'Within 18 Weeks' = CALCULATE ( [Waiting List], 'Wait Band'[Max Weeks] <= 18 )

It’s a bug. DAX comparison operators other than == treat BLANK as zero, and the open-ended 104+ weeks band has a blank upper edge, so the new version counts the longest waiters as within 18 weeks. A reviewer who knows the data catches that from two lines. Nobody catches it from a published report.

Properties next to the logic. formatString, isHidden, summarizeBy, displayFolder, dataType. A measure whose format changes from 0.0% to #,0 will display a percentage as 1 or 0.

Relationships. A new crossFilteringBehavior: bothDirections line, or isActive flipping, changes the answer of every measure that crosses that relationship. I ask for the reason in the pull request description.

Partitions. Changes to the M in a source = block change what gets loaded. A new column in the query needs a matching column in the table, with a dataType and sourceColumn.

Noise. Some changes carry no meaning: lineageTag lines, which Power BI Desktop typically adds as GUIDs for objects that don’t have one; annotations it maintains, such as PBI_QueryOrder or PBIDesktopVersion; and reordering. Commit that churn on its own, so it doesn’t bury real changes in someone else’s review.

A workflow that works

1. Save as a project. In Power BI Desktop, File > Save as > Power BI project. If your version still lists Store semantic model using TMDL format under Preview features, switch it on, or you get model.bim instead of the folder.

2. Commit the Desktop defaults. Desktop creates a .gitignore that excludes .pbi/localSettings.json and .pbi/cache.abf if none exists. The cache is a copy of the data. It must never reach the repository.

3. Branch per change. Edit in Desktop, in TMDL view, or in the files directly with the TMDL extension for VS Code. When the files change under an open project, Desktop detects it and offers to apply the external changes.

4. Check before merge. Loading the folder with Microsoft’s serializer catches bad indentation, unknown properties and references to objects that don’t exist. The check in the RTT project is a few lines of C#:

   var database = TmdlSerializer.DeserializeDatabaseFromFolder(path);
   

   Wrong syntax throws a TmdlFormatException, with the file and line number. Valid syntax that breaks the model’s rules, such as a sortByColumn naming a column that isn’t there, throws too. If your version of Tabular Editor reads TMDL, its Best Practice Analyzer can add rule checks on top; check the release notes for your version before relying on it in CI.

5. Deploy from the branch you reviewed. Fabric Git integration, the Fabric APIs, or Desktop’s publish all work from a project. Desktop’s publish also sends the local data cache; the other two send only metadata.

Gotchas

• Line endings. Power BI Desktop writes CRLF. Set core.autocrlf (or a .gitattributes rule) before the first commit, or every file shows as changed on another machine.
• Encoding. Files edited outside Desktop should be saved as UTF-8 without a byte order mark.
• Path length. Windows limits paths to 260 characters by default, and table names become file names. Keep the project near the root of a drive.
• Unapplied Power Query changes. If Desktop has saved query edits you chose to apply later (unappliedChanges.json), applying them overwrites any M you edited in the files meanwhile.
• Auto date/time. With it on, Desktop adds a hidden date table per date column, each as its own file. Microsoft says not to edit those externally. I switch the feature off in any model that goes into Git.
• Files you can’t edit. diagramLayout.json isn’t supported for external editing. Leave it to Desktop.
• Duplicated properties. TMDL lets you split an object across files, but a property declared twice is a parse error. Two people adding the same measure in different files will fail the load, which is the right outcome.
• Indentation style. In my test the serializer also read a copy of the model indented with four spaces, but it writes tabs. Hand-edit with tabs, or the next tool that rewrites the file turns your spaces back into tabs and the diff touches every line.
• Sensitivity labels aren’t supported in Power BI projects. Check your organisation’s labelling policy before moving a labelled model to PBIP.

What TMDL doesn’t check

Loading TMDL proves the model is well formed. It doesn’t evaluate any DAX or run any Power Query, so a measure that parses can still return the wrong number, as the diff above shows. For that you need a test that queries the model and compares the answer with one you know. In the RTT project that’s a pandas implementation of every KPI, plus a DAX query of all 18 measures that should reproduce it. DAX for waiting-list KPIs goes through those measures.