Power BI: Check before you delete

17 Nov, 2023

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 pbi-search ( [Parameter(Mandatory)] $keywords ) {

    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
    
                        $visual.prototypeQuery.Select.Name |
                            ForEach-Object {
                                $value = $_
        
                                $data.Add(
                                    [PSCustomobject] @{
                                        pageName = $pageName;
                                        visualType = $visualType;
                                        value = $value
                                    }
                                )
                            }               
                    }
            }
            
            return $data | ? { $null -ne $_.value  }
    }

    function searchReportForReference ($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 '*'
                
                $value = ($_.value)
                $value -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 $($_.value)
                        }
                }
        }
    
    }

    $path = (pbi-GetFiles).path

    # run for all project folders
    $projectName = $path.split('\')[-1]
    Write-Host "`n" -NoNewline
    Write-Host "In project " -NoNewline
    Write-Host -ForegroundColor Red $projectName
    $reportFile =  "$path\$projectName.Report\report.json"

    $data = importPowerBIReport $reportFile
    
    searchReportForReference $data $keywords

}

#pbip #powerbi #powershell