Adventures with Power Automate: Power BI Connector, Scorecards and Goals

A customer of mine was struggling with retrieving Goals from a Power BI Scorecard using Power Automate and the Power BI Connector, it kept generating the error! 😤

This is the error message.

The given key was not present in the dictionary. function: ProjectGoalValueAggregations last line: 11

I wanted to see I could replicate the challenge, so I created a sample Scorecard in a Power BI Workspace.

Figure 1 – Sample score card and custom statuses

It took a little bit to figure out what was causing the error, but I finally figured it out 🥳… it was the custom Statuses.

Figure 2 – Flow to retrieve goals for all scorecards

From what I can tell the Status, as far as how the Power BI Connector works, it seems to be restricting to values between 0 and 5.

Any additional Statuses that were added, generated an error, and I also note that any of the standard statuses that were renamed, would return the original name, even though it had been changed! 😤

I got to work on a possible workaround, starting with the HTTP Connector in Power Automate.

The first thing I needed to do was create an App Registration (referred to later as Service Principal) in Azure ☁️ with the appropriate API Permissions. 🔐

Dataset.Read.All
Dataset.ReadWrite.All

Figure 4 – App registration and permissions

I needed to Grant admin consent for the API Permission.

I also created a Client Secret that I will need to use in my HTTP calls.

I enabled Service principals can use Fabric APIs in the Power BI Admin Portal.

Click the Gear icon.
Search for principal in the Search Box.
Toggle Service principals can use Fabric APIs to Enabled.

Figure 5 – Enable service principals to use Fabric APIs

I gave my Service Principal Admin access to the Workspace.

Figure 6 – Grant service principal access to the workspace

Now I am ready to build the flow! 🙃

I created three Environment variables to store the Client Id, Client Secret and Tenant Id for the Service Principal.

I will share the Solution at the end of this article, but in the meantime, I will share the HTTP actions of the flow, as that is where the heavy lifting takes place.

Right now, I am using a Manually trigger a flow trigger that includes two parameters, GroupId, the unique identifier of the Workspace, and ScorecardId, the unique identifier of the Scorecard.