Create Power BI Metrics/Goals Scorecards from Excel files using Power Automate

I like Power BI scorecards a lot, as they give a really nice interface and look to your and your teams goals. However, entering all goals manually can become a bit tedious, especially if you have your goals already sitting in an Excel file. This is often the case for example with Budget figures. In this article I will share a method using Power Automate to create a scorecard automatically from an Excel file using standard Power Automate connectors. Please note that these connectors are still a bit limited, so connections to Power BI reports for example cannot be created currently.

Challenge for creating Power BI scorecards from Excel

The biggest challenge for this task I see are the goals hierarchies. Take a look at the sample data below:

Challenge: Create the parent-child-hierarchy with newly created IDs

The first 2 columns (id and parentid) determine the hierarchical structure of the goals. It would be great if we could use them in the flow to create the same connections in the Power BI goals. But unfortunately that is not possible: Once a goal is created through the connector, it is assigned a unique ID by the API and that ID must be used by the other goals to connect to it. This can be interpreted as a recursive task and in my previous blogpost I have described some techniques on how to solve that in Power Automate.

Recursive “For-each”-loop with an array variable

We can use an array-variable in the flow to store the “Old_ID”-“New_ID”-value pairs for all created goals. That allows for each newly created goal to lookup the correct parentid. Please refer to my previous blogpost to understand the general setup.

These are the flows’ steps:

Overview over the Flows’ steps

The following pictures display the details. In the comments you will see the formulas from the formula editor:

Sample connector

If you want to play around with it, please check out the template enclosed: CreatePowerBIScorecardfromExceltable_20221008170230.zip (2629 downloads )

I have created trigger parameters for the Excel-connection to make this work in different environments. But I find it generally easier to enter the connection details directly in the connector. So once you have imported the template into your environment feel free to delete these parameters and connect to your table directly in the Excel-connector.

Trigger parameters for the Excel-Connection

Enjoy the flow and stay queryious 😉