Breakthrough! Edit a model.bim file through PowerShell

10 Sep, 2024

Find and replace

I've always wanted to be able to edit a Power BI file through PowerShell. I've never succeeded until today!

Today, I was able to write a script that does a find-and-replace through several Power Query scripts within a model.bim file, and saves it back. The saving it back has always been point where I fail, but today I succeeded.

Soon, I'll write some functions so that you can edit Power Query and DAX as text files in a folder in VS Code, and make it write back to Power BI. Stay tuned!

$projectName = "project name"
$old = "old text"
$new = "new text"

$modelFilePath = '.\folder\$projectName\$projectName.SemanticModel\model.bim'

$modelFile = (cat "$modelFilePath" | convertfrom-json -depth 100)

$modelFile.model.tables |
    % {
        # remember that the expression property is an array, where every line is a separate element
        [string[]] $expression = $_.partitions.source.expression

        # for every line in the array "$expression"...
        $newExpression = $expression |
            foreach-object {
                # the main operation here is replacing some text
                $_.replace($old, $new)
            }

        # save it back in to the object
        $_.partitions.source.expression = $newExpression
    }

# save the whole thing back in to model.bim
$modelFile | convertto-json -depth 100 > "$modelFilePath"

Extra section: Graffiti

Writing back to every single PQ script programmatically also opens up the ability to tag every script with your name (or whatever else you like). Here, I compose a signature as a multi-line comment, which will be appended to the top of every PQ script to be found in the target data model.

I also have a marker so that I can check if a script has already been tagged - no use in tagging something twice.

# specify the folder where the .pbip lives
$projectFolder = ".\Project Name"

$projectName = $projectFolder.split('\')[-1]

$modelFile = "$projectFolder\$projectName.SemanticModel\model.bim"

$model = (cat $modelFile | convertfrom-json)

# $model
$marker = @("// # SIGNED")
$signature = @(
    "/*"
    "====================================="
    "MADE BY ME"
    "$(Get-Date -format "dd-MM-yyyy")"
    "====================================="
    "*/"
    ""
)

# unloaded PQ
$model.model.expressions |
    % {
        # if it hasn't been tagged yet, then tag it
        if (([string[]] $_.expression )[0] -ne $marker) {
            
            # put signed marker and signature at the top of the expression
            [string[]] $_.expression = $marker + $signature + $_.expression
        }
    }

# tables
$model.model.tables.partitions |
    ? {
        $_.source.type -eq "m"
    } |
    % {
        # if it hasn't been tagged yet, then tag it
        if (([string[]] $_.source.expression )[0] -ne $marker) {
            
            # put signed marker and signature at the top of the expression
            [string[]] $_.source.expression = $marker + $signature + $_.source.expression
        }
    }

# write back into the model file!
$model | convertto-json -depth 100 > $modelfile

#powerbi #powershell