Breakthrough! Edit a model.bim file through PowerShell
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"