Automatically check Power BI reports are using the right template with Azure DevOps

Reading Time: 7 minutes

In this post I want to show you how you can automatically check Power BI reports are using the right template with Azure DevOps. Like in the below diagram.

As far as the recommended CI/CD workflow options from Microsoft are concerned, you would implement this check as part of your development process. In order to shift-left and identify reports that are not using a certain template at an early stage.

I want to show how to automate this check since it is becoming more popular. In addition, automating tests like this can help with your governance story. Plus, they can be part of your journey to improve your Microsoft Fabric Continuous Integration maturity level.

To manage expectations, this post covers how to automatically check that Power BI reports are using the right template by checking a custom theme. You can change the logic to cater for checking for existence of other items in your template like logos instead.

Key points about this post

Some key points I want to highlight about this post before I go any further:

• For the benefit of this post, I am showing a GUI-based classic pipeline in Azure DevOps. In reality, you should be creating a YAML Pipeline instead.
• It would have been really easy for me to check the theme based on a Python script I showed in my previous post. However, some well-known Power BI Professionals are advocating for working with PBI Inspector. Which is why I decided to show this method instead.
• I show the custom PBI Inspector rules independently in this post. Feel free to merge the logic into other solutions available online though. For example, the fabric-cli-powerbi-cicd-sample example made available by Rui Romano.
• Logic to run the pipeline shown in this post was adapted from the Power BI Project (PBIP) and Azure DevOps build pipelines for validation guide by Microsoft.

Along the way I share plenty of links. If you need help with any jargon, then I recommend that you read one my other posts. Which is a Microsoft Fabric Git integration jargon guide for Fabricators.

Prerequisites

In order to follow along with this post you will need the following:

• A Git repository created in the Azure Repos service in Azure DevOps.
• Either a Fabric capacity or a Power BI Premium capacity so that you can configure Microsoft Fabric Git integration on workspaces that link to your created repository.
• Power BI reports that are saved as Power BI Desktop Projects locally when working with Power BI Desktop. Alternatively, a report created in a workspace with Microsoft Fabric Git integration configured.
• If working with Power BI reports locally, then recommend you install both Git and Visual Studio Code.

Ideally, you should also create a report template if you intend to follow along with this post.

For example, I selected a built-in report theme and then selected to customize the theme and gave it a new name.

I then customized my template further by adding a Microsoft Fabric image to it as below before exporting the file as a template.

Creating initial Power BI report and PBI Inspector tests to check Power BI reports are using the right template

I created a new Power BI report based on the template file. Checking manually that the report had the right theme specified.

Once I had verified the theme, I added a text box and then saved the report as a Power BI Desktop Project.

Afterwards, I went into Visual Studio Code, opened up the folder that contained my Power BI Desktop Project. I then checked what the details for the custom theme were. Since the json file for the theme contains additional numeric value after the theme name.

PBI Inspector rules to check the template

Once that was done, I created a new subfolder called CustomRules. Within my new subfolder I created a new “check-template.json” file which contained the below PBI Inspector rules.

{
  "pbiEntries": [
    {
      "name": "Report theme",
      "description": "Check report theme properties.",
      "pbixEntryPath": "Report/StaticResources/RegisteredResources/PlentyOfGreen2438104580276158.json",
      "pbipEntryPath": "StaticResources/RegisteredResources/PlentyOfGreen2438104580276158.json",
      "contentType": "json",
      "codepage": 65001,
      "rules": [
        {
          "id": "REPORT_THEME_NAME",
          "name": "Report theme name",
          "description": "Check Report theme name",
          "disabled": false,
          "logType": "error",
          "path": "$",
          "pathErrorWhenNoMatch": true,
          "test": [
            {
              "==": [
                { "var": "n" },
                "PlentyOfGreen"
              ]
            },
            {
              "n": "/name"
            },
            true
          ]
        }
      ]
    },
    {
      "name": "Correct report theme specified",
      "description": "Check correct report theme specified.",
      "pbixEntryPath": "Report/Layout",
      "pbipEntryPath": "report.json",
      "contentType": "json",
      "codepage": 6500,
      "rules": [
        {
          "id": "CHECK_CORRECT_THEME",
          "name": "Check correct theme",
          "description": "Check correct theme specified",
          "disabled": false,
          "logType": "error",
          "path": "$",
          "pathErrorWhenNoMatch": true,
          "test": [
            {
              "==": [
                { "var": "n" },
                "PlentyOfGreen2438104580276158.json"
              ]
            },
            {
              "n": "/theme"
            },
            true
          ]
        }
      ]
    }
  ]
}

I opted to add two rules for certainty:

• First rule checks that the theme has been imported as part of the Power BI Desktop Project based on the file name. Plus, it checks that the file contains the correct theme name.
• Second rule checks to make sure that the report itself states the correct theme based on its filename.

After I added the file, I then initialized my Power BI Project as a Git repository and synchronized it with an empty repository in Azure DevOps.

Automatically check Power BI reports are using the right template with Azure DevOps

After checking the Azure DevOps repository contained files, I created a new Microsoft Fabric workspace and setup Microsoft Fabric Git integration to a dev branch.

Which then populated the workspace with my Power BI report. Once my dev workspace was configured, I wanted to ensure that the PBI Inspector rules were run every time somebody wanted to update my dev branch.

To configure this, I went back into Azure DevOps and created the below pipeline.

First stage is a subset of code found in ContinuousIntegration-Rules YAML file specified in the Microsoft article. Which downloads the zip file for PBI Inspector and extracts it for use.

$path = "$(Build.SourcesDirectory)"
$tempPath = "$path\_temp"
$toolPath = "$path\_Tools\PBIInspector"
New-Item -ItemType Directory -Path $tempPath -ErrorAction SilentlyContinue | Out-Null

Write-Host "##[debug]Downloading PBI Inspector"
$downloadUrl = "https://github.com/NatVanG/PBI-Inspector/releases/latest/download/win-x64-CLI.zip" 
$zipFile = "$tempPath\PBIXInspector.zip"
Invoke-WebRequest -Uri $downloadUrl -OutFile $zipFile
Expand-Archive -Path $zipFile -DestinationPath $toolPath -Force                            

Second stage contains a modified version of the code found in the Microsoft article. I modified it so that it only runs the custom rules that I created instead of the default rules.

$path = "$(Build.SourcesDirectory)"
Write-Host "Source Directory is $(Build.SourcesDirectory)"
# $path = "C:\Temp"
$tempPath = "$path\_temp"
$toolPath =  "$path\_Tools\PBIInspector\win-x64\CLI\PBIXInspectorCLI.exe"
$rulesPath = "$(Build.SourcesDirectory)\CustomRules\check-template.json"
Write-Host "Rules Path is $rulesPath"

$itemsFolders = Get-ChildItem  -Path $sourcepath -recurse -include *.pbir

foreach($itemFolder in $itemsFolders)
{
    $itemPath = $itemFolder.Directory.FullName
    
    Write-Host "##[group]Running rules for: '$itemPath'"

    Start-Process -FilePath "$toolPath" -ArgumentList "-pbipreport ""$itemPath"" -rules ""$rulesPath"" -formats ""ADO""" -NoNewWindow -Wait
    
    Write-Host "##[endgroup]"
}

I then defined a branch policy the same way as described in the original Microsoft article.

Once the development workspace was in-place I was ready to start testing everything.

Testing that Power BI reports are using the right template

First of all, I used the Branch out to new workspace functionality to create a new feature branch and workspace.

Doing so populated the feature workspace with my original report. I made a basic change to report in my feature workspace and committed the change.

Afterwards, I went to the repository in Azure DevOps to create a Pull Request to merge the update from the feature branch to the dev branch. It ran my pipeline as part of the check, which completed successfully.

I then uploaded a PBIX file of a report that did not use the theme. Which automatically created a new Power BI report in my feature workspace When I went to complete a Pull Request from the feature branch to the dev branch it failed with the below error.

I then performed other checks. Including publishing another report that was created with the same template which passed. In addition, I also changed the theme name in my rules which also caused a failed test.

After multiple checks I concluded that the check was working as it should.

Final words

I hope that by this post on how to automatically check Power BI reports are using the right template with Azure DevOps helps some of you. In addition, I hope it inspires some of you to think about how to improve your testing strategies.

Like I mentioned earlier in the post, there are other ways you can check you that reports are working with the right template. For example, with Python scripts. I suspect the method in this post will be more popular within the Power BI community though.

Of course, if you have any comments or queries about this post feel free to reach out to me.