Archive and QA PBI Workspaces with PowerShell

I was working on something where I had to archive and review workspace content. I wanted to do it quickly and I also wanted to have a robust solution to take care of QA checks as well. I’ve definitely seen this done before in pieces and wanted to try and see if I could put some things together.

Need #1: Download All Pbix Files and Flows From All Defined Workspace

First thing’s first. How do you download files from a workspace?

The MicrosoftPowerBIMgmt PowerShell library is what I used. Specifically, Export-PowerBIReport and Export-PowerBIDataFlow. Run this after connecting to your environment. For a public environment, you can connect easily with something like this:

Login-PowerBI -Environment Public

An authentication window will pop up and you can just sign in. There are obviously ways to sign in with a service principal, but for my purposes that’s a little too much for what I am really doing. I am not scheduling this from a virtual machine or anything like that.

I wanted the code to loop through any workspaces I defined in an array (by name) upfront. Reason being, you may only want to spot check so workspaces and not others.

Need #2: Extract BIM Files

Brian Julius had a great LinkedIn post about how he used AI to prompt a .bat file that generated .bim files using pbi-tools. I thought it was bonkers at the time and saved the code. When I started this project, I thought it would be cool to run this after downloading the pbix files and generating the .bim files.

The function ended up looking like this:

Function Add-ExtractFiles {
    param(
        [Parameter(Mandatory=$true)]
        [string]$ExtractFolderLocation,
        [Parameter(Mandatory=$true)]
        [string]$PbixFile
    )

    # Check if the PBIX file exists
    if (Test-Path $PbixFile) {

        # Get the filename without extension from the PbixFile path
        $filename = [System.IO.Path]::GetFileNameWithoutExtension($PbixFile)
        # Define the extract folder path
        $extract = $ExtractFolderLocation + "\" + $fileName
        # Create the extract folder
        New-Item -Path $extract -ItemType "Directory"

        # Extract the PBIX file contents to the extract folder
        pbi-tools extract $PbixFile -extractFolder $extract
        # Generate the BIM file from the extracted contents
        pbi-tools generate-bim -folder $extract

        # Delete all contents inside the extract folder
        Remove-Item -Path "$extract\*" -Recurse -Force
        # Delete the extract folder itself
        Remove-Item -Path $extract -Recurse -Force

        # Notify the user that BIM files were created successfully
        Write-Output "Bim files generated successfully."

    } else {
        # Notify the user that the PBIX file does not exist
        Write-Output "The file $PbixFile does not exist."
    }
}

Need #3: Run BPA Rules

Feeling brave, I thought, “Could you run Tabular Editor from the command line?”

Turns out, yes you can with Tabular Editor 2. It actually doesn’t even require much code. Running it and producing a csv was a one-liner (after putting together file names):

# Run the Tabular Editor BPA and save the results to the output file
    & $TabularEditorPath $BimFile -A | Out-File -FilePath $BPAOutputFile

This post by Ben Fedit helped out a ton.

Need #4: Run PBI Inspector

At this point, I thought, “Could I also add in PBI Inspector?”

Yup, you can. I knew the answer because John Kerski posted about doing so on LinkedIn. I remember being very excited about PBI Inspector when it came out, but got sidetracked with life I guess and never got to using it much. It is a fantastic tool now that I can see all of the automation capabilities it provides.

Once I was able to run the .exe and pass in the parameters, this part ran so quickly and it provides a robust HTML file and an image so that you can see which visual failed. Really amazing stuff.

Need #5: Folder Structure for All This

As part of this, I had to set up a folder structure, so I made a function to create folders if they already did not exist. Here is an example folder structure after a completed run:

─Workspace
    ├───BPA
    ├───Extracts
    ├───Flows
    ├───Inspector
    │   ├───Report1
    │   ├───Report2
    │   ├───Report3
    │   ├───Report4
    │   └───Report5
    └───Reports

In the PBI Inspector folder, subfolders are created to house nested output from PBI Inspector. The rest of the folders contain files (.bim files are in the Extracts folder).

Running It

I saved this as a .psm1 (module) file and in a different .ps1 file, I imported and ran it like so:

Import-Module "C:\PATH\TO\MODULE.psm1"

$Workspaces = @('Workspace1', 'Workspace2')
$Basepath = "C:\PATH\TO\ARCHIVE\LOCATION"
$PBIInspectorPath = "C:\PATH\TO\PBI\INSPECTOR\FOLDER"
$RulesFile = "C:\PATH\TO\PBI\INSPECTOR\RULES\FILE"
$TabularEditorPath = "C:\PATH\TO\TABULAR\EDITOR\2\EXE"


Export-Workspace -BasePath $BasePath -Workspaces $Workspaces -PBIInspectorPath $PBIInspectorPath -RulesFile $RulesFile -TabularEditorPath $TabularEditorPath

How Did I Do This?

Some GitHub Copilot, a lot of paying attention to LinkedIn, some reading PowerShell books, and web searching helped me put this all together. Copilot definitely helped a lot, but there were times when it spit out code and it would not work. That’s where searching blogs, remembering who posted what on LinkedIn, and referencing books helped out a ton.

Wrap Up

Here is the gist with the PowerShell module.

I have been running this on Pro (not Premium) Workspaces and it works great for me. I am sure there are ways to extend this further for Premium Capacity Workspaces, but I focused on regular, plain-old workspaces because that is what I play it at home.

Thanks to everyone that shares content on the web and on LinkedIn and also to all the people who released community software that helped with this.