Power BI: Rip Power Query and DAX code

09 Nov, 2023

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.

Here are two PowerShell functions that will rip out all of the Power Query and DAX.

function rippq ($projectName, $modelFile) {
    # $modelFile is the path to the model.bim file
    Write-Host -ForegroundColor Cyan "Reading model.bim"
    $model = Get-Content $modelFile | ConvertFrom-Json
    
    $dest = "$codeRepo\$projectName\pq"

    if ((Test-Path $dest) -eq $false) {
        New-Item -ItemType Directory $dest | Out-Null
    }

    $model.model.tables |
        # Select-Object name, annotations |
        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
        }

    $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 $codeRepo\pq"
    
}

function ripdax ($projectName, $modelFile) {
    # $modelFile is the path to the model.bim file
    Write-Host -ForegroundColor Cyan "Reading model.bim"
    $model = Get-Content $modelFile | ConvertFrom-Json
    
    $dest = "$codeRepo\$projectName\dax"

    if ((Test-Path $dest) -eq $false) {
        New-Item -ItemType Directory $dest | Out-Null
    }
    
    $model.model.tables | 
        Where-Object {
            $null -ne $_.measures
        } |
        ForEach-Object {
            $tableName = $_.name
            $path = "$codeRepo\$projectName\dax\$tableName\Measures"

            $_.measures |
                ForEach-Object {
                    if ((Test-Path $path) -eq $false) { New-Item -ItemType Directory $path | Out-Null }
                    $name = ($_.name) -replace [RegEx]::Escape('/'), '' -replace [RegEx]::Escape(':'), ''
                    $expression = -join "$($_.name) = ", $($_.expression)
                    Set-Content -Path "$path\$name.dax" -Value $expression
                }
        }

    $model.model.tables |
        Where-Object {
            $_.annotations.name -eq 'PBI_ResultType'
        } |
        ForEach-Object {
            $tableName = $_.name
            $path = "$codeRepo\$projectName\dax\$tableName\Calculated Columns"
            
            # $tableName
            $_.columns |
                ? { $_.type -eq 'calculated' } |
                % {
                    if ((Test-Path $path) -eq $false) { New-Item -ItemType Directory $path | Out-Null }
                    $name = ($_.name) -replace [RegEx]::Escape('/'), '' -replace [RegEx]::Escape(':'), ''
                    $expression = -join "$($_.name) = ", $($_.expression)
                    Set-Content -Path "$path\$name.dax" -Value $expression
                }
    
        }

    Write-Host -ForegroundColor Cyan "Code ripped and stored in $codeRepo\dax"
    
}

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