Dyota's blog

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.

#dax #pbip #powerbi #powerquery #powershell