Power BI: Rip Power Query and DAX code
Updated 22nd October 2024
I discovered that you can save a Power BI file as a project file some time ago, but it wasn't until yesterday that I really did something with it.
One of the things that you can access when the file is saved as a .pbip file is the definitions of Power Query scripts and DAX formulas.
function pbi-ripCode ($path) {
# takes a .pbip folder, rips out all of the PQ and DAX, and stores it inside the same folder as $projectname.CodeFiles
function rippq ([string] $dest, [PSCustomObject] $model) {
# given a the contents of a model.bim file ($model, parsed from in JSON), rip out all of the Power Query code and save it in $dest
Write-Host -ForegroundColor Cyan "Ripping PQ"
if ((Test-Path $dest) -eq $false) { New-Item -ItemType Directory $dest | Out-Null }
# these are PQ scripts that are loaded to the data model as tables
$model.model.tables |
Where-Object {
$_.annotations.name -eq 'PBI_ResultType'
} |
ForEach-Object {
$queryGroup = $_.partitions.queryGroup
$path = "$dest\$queryGroup"
if ((Test-Path $path) -eq $false) { New-Item -ItemType Directory $path | Out-Null }
Set-Content -Path "$path\$($_.name).pq" -Value $_.partitions.source.expression
}
# these are PQ scripts that are _not_ loaded to the data model (tables or otherwise)
$model.model.expressions |
ForEach-Object {
$queryGroup = $_.queryGroup
$path = "$dest\$queryGroup"
if ((Test-Path $path) -eq $false) { New-Item -ItemType Directory $path | Out-Null }
Set-Content -Path "$path\$($_.name).pq" -Value $_.expression
}
Write-Host -ForegroundColor Cyan "Code ripped and stored in " -NoNewline
Write-Host -ForegroundColor Green "$dest"
}
function ripdax ($dest, $model) {
Write-Host -ForegroundColor Cyan "Ripping DAX"
if ((Test-Path $dest) -eq $false) { New-Item -ItemType Directory $dest | Out-Null }
# measures
$model.model.tables |
Where-Object {
$null -ne $_.measures
} |
ForEach-Object {
$tableName = $_.name
$path = "$dest\$tableName\Measures"
$pathKpis = "$dest\$tableName\KPIs"
$_.measures |
ForEach-Object {
if ((Test-Path $path) -eq $false) { New-Item -ItemType Directory $path | Out-Null }
# illegal characters in filenames
$name = ($_.name) -replace [RegEx]::Escape('/'), '' -replace [RegEx]::Escape(':'), '' -replace [RegEx]::Escape('>'), ''
# append the name of the measure
$expression = -join "$($_.name) = ", $($_.expression)
Set-Content -Path "$path\$name.dax" -Value $expression
if ($null -ne $_.kpi) {
if ((Test-Path "$pathKpis\$name") -eq $false) { New-Item -ItemType Directory "$pathKpis\$name" | Out-Null }
$kpi = $_.kpi
if ($null -ne $kpi.statusExpression) {
$status = "// $($kpi.statusDescription)`n$($kpi.statusExpression -join "`n")"
Set-Content -Path "$pathKpis\$name\$name.Status.dax" -Value $status
}
if ($null -ne $kpi.targetExpression) {
$target = "// $($kpi.targetDescription)`n$($kpi.targetExpression -join "`n")"
Set-Content -Path "$pathKpis\$name\$name.Target.dax" -Value $target
}
if ($null -ne $kpi.trendExpression) {
$trend = "// $($kpi.trendDescription)`n$($kpi.trendExpression -join "`n")"
Set-Content -Path "$pathKpis\$name\$name.Trend.dax" -Value $trend
}
Write-Host $name has KPIs
}
}
}
# calculated columns
$model.model.tables |
Where-Object {
$_.annotations.name -eq 'PBI_ResultType'
} |
ForEach-Object {
$tableName = $_.name
$path = "$dest\$tableName\Calculated Columns"
# $tableName
$_.columns |
Where-Object { $_.type -eq 'calculated' } |
ForEach-Object {
if ((Test-Path $path) -eq $false) { New-Item -ItemType Directory $path | Out-Null }
# illegal characters in filenames
$name = ($_.name) -replace [RegEx]::Escape('/'), '' -replace [RegEx]::Escape(':'), ''
# append the name of the measure
$expression = -join "$($_.name) = ", $($_.expression)
Set-Content -Path "$path\$name.dax" -Value $expression
}
}
# calculated tables
$model.model.tables.partitions |
Where-Object {
($_.source.type -eq 'calculated') -and
-not ($_.name -like "LocalDateTable*") -and
-not ($_.name -like "DateTableTemplate*")
} |
ForEach-Object {
$tableName = $_.name
$path = "$dest\Calculated Tables"
if ((Test-Path $path) -eq $false) { New-Item -ItemType Directory $path | Out-Null }
$expression = $_.source.expression
Set-Content -Path "$path\$tableName.dax" -Value $expression
}
# calculation groups
$model.model.tables |
Where-Object {
$null -ne $_.calculationGroup
} |
ForEach-Object {
$tableName = $_.name
$path = "$dest\$tableName\Calculation Groups"
if ((Test-Path $path) -eq $false) { New-Item -ItemType Directory $path | Out-Null }
$_.calculationGroup.calculationItems |
% {
$calcName = $_.name
$expression = $_.expression
Set-Content -Path "$path\$calcName.dax" -Value $expression
}
}
Write-Host -ForegroundColor Cyan "Code ripped and stored in " -NoNewLine
Write-Host -ForegroundColor Green "$dest"
}
function riprls ($dest, $model) {
# if there is RLS, then do
if ($null -ne $model.model.roles) {
Write-Host -ForegroundColor Cyan "Ripping RLS"
$out = [System.Text.StringBuilder]::new()
$model.model.roles |
select name, tablePermissions |
% {
$role = $_.name
[void] $out.AppendLine($role)
$_.tablePermissions |
% {
[void] $out.AppendLine("`t$($_.name): $($_.filterExpression)")
}
}
$out.ToString().Trim() > "$dest\rls.txt"
}
}
function ripDaxReport($dest, $report) {
# this is if the report is a child of a semantic model and has measures of its own
$config = $report.config | ConvertFrom-Json
$config |
ForEach-Object {
$entities = $_.modelExtensions.entities
if ($entities.Count -gt 0) {
$entities |
ForEach-Object {
$tableName = $_.name
$dest = "$dest\$tableName\Measures"
if ((Test-Path $dest) -eq $false) { New-Item -ItemType Directory $dest | Out-Null }
$_.measures |
ForEach-Object {
$name = ($_.name) -replace [RegEx]::Escape('/'), '' -replace [RegEx]::Escape(':'), ''
$expression = -join "$($_.name) = ", $($_.expression)
Set-Content -Path "$dest\$name.dax" -Value $expression
}
Write-Host -ForegroundColor Cyan "Code ripped and stored in " -NoNewline
Write-Host -ForegroundColor Green "$dest"
}
}
}
}
function ripReport ($dest, $report) {
# each "section" is a page in the Power BI report
$sections = $report.sections
Write-Host -ForegroundColor Cyan "I see $($sections.Count) pages"
Write-Host -ForegroundColor Cyan "Ripping pages"
$sections |
% {
$pageName = $_.displayName.Trim() -replace [RegEx]::Escape('/'), '' -replace [RegEx]::Escape(':'), ''
$path = "$dest\$pageName"
if (!(Test-Path "$path")) { New-Item -ItemType Directory $path | Out-Null }
# a 'container' is a visual on the page
$visuals = $_.visualContainers
$visuals |
% {
$visualConfig = $_.config | ConvertFrom-Json
$name = $visualConfig.name
$type = $visualConfig.singleVisual.visualType
if ($null -ne $type) {
# depth needs to be set to a big number (20) because lots of nested properties
Set-Content -Path "$path\$type $name config.json" -Value ($visualConfig | ConvertTo-Json -Depth 20)
}
}
}
Write-Host -ForegroundColor Cyan "All objects from all pages ripped"
}
function riprelationships ($dest, $model) {
# all relationships are defined in "relationships"
$relationships = $model.model.relationships
# tables contain further definition for columns - we need data type for the dbml file format
$tables = $model.model.tables |
ForEach-Object {
$_.columns = $_.columns | Select-Object name, dataType
$_ | Select-Object name, columns
}
# set up stringbuilder for final output to file
$sb = [StringBuilder]::new()
# set up a temporary collection of tables and columns to iterate over later
# this is to make sure that only the columns involved in relationships are picked up, and no more
# and also to make sure there are no duplicates in columns
[List[PSCustomObject]] $tablesCollection = @()
# fill in $tablesCollection
$relationships |
ForEach-Object {
$tablesCollection.Add( @{table = $_.fromTable; column = $_.fromColumn; } )
$tablesCollection.Add( @{table = $_.toTable; column = $_.toColumn; } )
}
# if table or column names contain spaces, the need to be double-quoted
function quote ($text) {
$condition = $text.Contains(" ") -or $text.Contains("-")
if ($condition) { return "`"$text`"" } else { return $text }
}
# process over $tablesCollection
# this block will write out all of the table definitions for dbml
$tablesCollection |
Group-Object table |
ForEach-Object {
$columns = [StringBuilder]::new()
$tableName = $_.Name
$_.Group |
Select-Object -Unique column |
ForEach-Object {
$columnName = $_.column
$dataType = ( ($tables | Where-Object { $_.name -eq $tableName }).columns | Where-Object { $_.name -eq $columnName } ).dataType
[void] $columns.AppendLine("`t$(quote $columnName) $dataType")
}
$out = @"
Table $(quote $tableName) {
$($columns.ToString())
}
"@
[void] $sb.AppendLine($out)
}
# this block will write out all of the relationship definitions for dbml, at the bottom of the file
# note that it is assumed that all relationships are in the > direction. I don't know where the relationship type property lives in model.bim
# note that active/inactive relationship states are not captured here
$relationships |
Sort-Object fromTable, fromColumn |
ForEach-Object {
[void] $sb.AppendLine( "Ref: $(quote $_.fromTable).$(quote $_.fromColumn) > $(quote $_.toTable).$(quote $_.toColumn)" )
}
# write to file
$sb.ToString() > "$dest\relationships.dbml"
}
$projectName = $path.split('\')[-1]
Write-Host -ForegroundColor Yellow "Ripping " -NoNewline
Write-Host -ForegroundColor Green "$projectName"
$modelFile = "$path\$projectName.SemanticModel\model.bim"
if ((Test-Path $modelFile) -eq $true) {
# put in argument -AshHashTable because some property names are empty strings (e.g. "": { ... })
$model = cat $modelFile | ConvertFrom-Json -AsHashtable
Write-Host -ForegroundColor Cyan "Reading model.bim"
rippq "$path\$projectName.CodeFiles\pq" $model
ripdax "$path\$projectName.CodeFiles\dax" $model
riprls "$path\$projectName.CodeFiles" $model
riprelationships "$path\$projectName.CodeFiles" $model
}
$reportFile = "$path\$projectName.Report\report.json"
if ((Test-Path $reportFile) -eq $true) {
# put in argument -AshHashTable because some property names are empty strings (e.g. "": { ... })
Write-Host -ForegroundColor Cyan "Reading report.json"
$report = cat $reportFile | ConvertFrom-Json -AsHashtable
ripDaxReport "$path\$projectName.CodeFiles\dax" $report
ripReport "$path\$projectName.CodeFiles\report" $report
}
}
Uses
Version control
Once all of the code is in its own file, you can do version control (e.g. Git).
Quick reference
It's a bit cumbersome sometimes to open up a whole .pbix file and look up the definition of a table or a measure, especially if the file size is a bit large. This way, you can open up the code in a text editor and refer to your code quickly.