Power BI is a widely used reporting tool in the market today. A Power BI Desktop file contains all DAX measures and calculated columns to build the visualizations using the data model. With growing calculated measures and columns, sometimes, we might need to export them into a file or repository for documentation purpose. This article describes how to export measures from Power BI Desktop using the Power BI template.
What is a Power BI template file?
The PBIT file keeps your report structure and contains ‘DataModelSchema File’ instead of ‘‘DataModel file. This means it doesn’t include any data from your source systems, and the size of this file is very small. However, it has Report pages, visuals, data model definitions, and query definitions. Read more about it here.
Steps to export measures from Power BI file.
1. Save the Power BI file as Power BI template (.pbit)
1. Open the Power BI (.pbix) file in PowerBI Desktop.
2. Click on the File menu item and click Save. Click ‘Browse this device’ button at the bottom left side.
3. Select the Template (.pbit) file type and give a name to the Power BI template file as shown in the below image.
2. Run below PowerShell script.
Copy the below PowerShell script in PowerShell ISE and provide the path to the folder containing the Power BI template file (.pbit) exported in step 1 to variable $pbitFolderPath. Also, give the name of the template file to the $pbiFileName variable.
#Path containing pbit file
#Parameters
################################################################
$pbitFolderPath = "D:\PowerBI files\"
$pbiFileName = "Sample PBI"
################################################################
$pbitFileName = $pbiFileName + ".pbit"
$pbitFilePath = $pbitFolderPath + $pbiFileName + ".pbit"
$zipFileName = $pbiFileName + ".zip";
$exportMeasuresFilePath = $pbitFolderPath +$pbiFileName + ' Calculations.csv'
$zipFileFullPath = $pbitFolderPath + $zipFileName
#Rename pbit file to zip file.
Rename-Item -Path $pbitFilePath -NewName $zipFileName
#Temp. export file path
$exportPath = $pbitFolderPath + "export\"
#Unzip zip file
Expand-Archive -Path $zipFileFullPath -DestinationPath $exportPath
#DataModelSchema file full path
$dataModelSchemaFullPath = $exportPath + "DataModelSchema"
$strFileName = "DataModelSchema.json"
#Rename DataModelSchema file to DataModelSchema.json
Rename-Item -Path $dataModelSchemaFullPath -NewName $strFileName
#Get the JSON from DataModelSchema.json
$dataModelSchemaJsonFullPath = $exportPath + $strFileName
$jsonStr = Get-Content $dataModelSchemaJsonFullPath -Encoding unicode | Out-String
#Convert the Json to a dynamic object
$outJson = ConvertFrom-Json $jsonStr
#Extract the tables to a list
$outTables = $outJson.Model.tables
"Table Name, Calculation Type, Calculation Name, Value" | Out-File $exportMeasuresFilePath
#Remove hidden system generated date tables.
$outTables = $outTables | Where-Object {$_.isHidden -ne $true}
foreach ($outTable in $outTables)
# Foreach Table in Power BI desktop
{
if ($outTable.measures -ne $null)
{
$outMeasures = $outTable.measures
foreach ($outMeasure in $outMeasures)
{
$("{0} , {1} , {2}, {3}" -f $outTable.name, "Measure", $outMeasure.name, $outMeasure.expression -join " " | Out-String) | Out-File $exportMeasuresFilePath -Append
# Write the measure and expression
}
}
if ($outTable.columns -ne $null)
{
$outColumns = $outTable.columns
$outColumns = $outColumns | Where-Object {$_.type -eq "calculated"}
# Filter on calculated columns only, but this could be removed for all
foreach ($outColumn in $outColumns)
{
$("{0} , {1} , {2}, {3}" -f $outTable.name, "Calculated column" , $outColumn.name, $outColumn.expression -join " ") | Out-File $exportMeasuresFilePath -Append
}
}
}
write-host "Calculations written to "$exportMeasuresFilePath
Rename-Item -Path $zipFileFullPath -NewName $pbitFileName
#Clenaup temp files
Remove-Item $exportPath -Recurse
The output of the above script execution will be stored in ‘<PowerBI filename> calculation.csv’ file in the same folder containing the Power BI template file. The result might not be well-formatted csv as the exported calculations can have ‘,’ in its definitions.
Pro tips:
1. Output of the above PowerShell script also includes calculated column expressions.
2. If you are using DAX Studio, you can also export measures from Power BI using Dax Studio
3. To test row-level security, you can read this post if you want to run the Power BI report as a different user.
See more
Kunal Rathi
With over a decade of experience in data engineering and analytics, I've assisted countless clients in gaining valuable insights from their data. As a dedicated supporter of Data, Cloud and DevOps, I'm excited to connect with individuals who share my passion for this field. If my work resonates with you, we can talk and collaborate.
I am always interested in new challenges so if you need consulting help, reach me at kunalrathi55@gmail.com.