Power BI: Check before you delete
One trap that I seem to repeatedly fall into is deleting a measure, or a column, or a table, with no visibility of the consequences, and doing it anyway.
My main Power BI file has grown into such a monster that I can't fit all of it in my head, and many times in the past, I had deleted something while "pruning" (i.e. I thought that measure was not necessary any longer), only to find out that it there was a report somewhere with a table that referred to it. Nevermind deleting, renaming columns and measures will have the same effect.
I now have a method of checking before deleting. This methods assumes that all of your Power BI files are saved as .pbip files, not as .pbix. This is because it relies on the files report.json that in contained inside the project folder.
I have all of my Power BI projects stored in a single location. The following PowerShell function lets me:
- enter some keywords (e.g. the name of a measure I'm about to delete)
- scan through all of the projects that I have, and
- identify all of the visuals that it appears in
using namespace System.Collections.Generic; # for List
function searchPbi ($keywords) {
$root = '.\Project Files'
function importPowerBIReport ($filePath){
# $filePath is the path for report.json
$report = Get-Content $filePath | ConvertFrom-Json
# set up list for data storage
[List[pscustomobject]] $data = @()
# one "section" is a page in the PBI report
$report.sections |
ForEach-Object {
$pageName = $_.displayName
# a "visualContainer" is a visual on the page
$_.visualContainers.config |
ForEach-Object {
$config = $_ | ConvertFrom-Json
$visual = $config.singleVisual
$visualType = $visual.visualType # tableEx, pivotTable, slicer, etc.
$visual.projections.Values |
ForEach-Object {
# queryRefs are in the form of table.column or table.measure
$queryRef = $_.queryRef
# put it into the data List
$data.Add(
[PSCustomobject] @{
pageName = $pageName;
visualType = $visualType;
queryRef = $queryRef
}
)
}
}
}
return $data
}
function searchReportForQueryReference ($data, $keywords) {
# take in the data collected from previous function, and
# filter it according to the keywords
$refsFound = [pscustomobject] $data |
Where-Object {
$keywords = $keywords -join '*'
$queryRef = ($_.queryRef)
$queryRef -like "*$keywords*"
}
# display a count of how many references found
Write-Host $refsFound.Count references found
# if there are no references, then do nothing
if ($refsFound.Count -gt 0) {
$refsFound | Group-Object -Property 'pageName' |
ForEach-Object {
# for each page...
Write-Host "On page " -NoNewline
Write-Host -Foregroundcolor Green $($_.Name)
$_.Group |
ForEach-Object {
# list out all of the references found on this page
Write-Host "On a " -NoNewline
Write-Host -Foregroundcolor Yellow $($_.visualType) -NoNewline
Write-Host " containing " -NoNewline
Write-Host -Foregroundcolor Yellow $($_.queryRef)
}
}
}
}
Get-ChildItem $root |
ForEach-Object {
# run for all project folders
$projectName = $_.Name
Write-Host "`n" -NoNewline
Write-Host "In project " -NoNewline
Write-Host -ForegroundColor Red $projectName
$reportFile = "$root\$projectName\$projectName.Report\report.json"
$data = importPowerBIReport $reportFile
searchReportForQueryReference $data $keywords
}
}