Listing a maker’s apps and flows from the CoE Starter Kit’s Power BI dataset with Power Automate

I bet you’re reading the pretty mouthful topic of this blog post and wondering “what the heck is this about”. You might also be wondering why use Power Automate for something like this.

Let me give you some context: My colleague Jukka and I had a tenant to tenant migration case, where we needed to establish which apps and flows would need to be migrated to a new tenant. Reading the CoE Starter Kit’s Power BI dataset for app and flow owners was one of several options we had for eventually establishing which apps and flows are to be migrated.

The approach we eventually chose was slightly different that the one I’m writing about here, but the core idea of listing apps and flows per maker remained. In short, the use case I’m writing about in this post is: List a maker’s apps and flows and send them an email asking which of their apps and flows are to be migrated.

To migrate or not to migrate – The cloud flow

The use case is straightforward and simple and we’ve now covered that. The rest is purely flow technical. If you’re still wondering why I’m writing about querying a PBI dataset even though we actually took a different path, the reason is two-fold: The migration case is still in progress. When it’s done I’ll have the lessons learned from the approach we took. The other reason is that I just think querying PBI datasets is pretty cool! It’s also a fairly new~ish feature in Power Automate.

Enough talking. Time to dissect our flow. On a high level, this is what we’re doing:

• List our makers.
• List our makers’ apps and flows.
• Send an email to a maker listing their apps and flows.

In our actual migration case we’re providing makers a means of marking apps and flows as “migrate” or “no migration” but that’s not in the scope of this post.

Flow’s initial actions

Whatever the chosen method of triggering the flow, the first step is is to initialize an array variable. This variable will hold a maker’s apps and flows down the line. The initial steps also include the Power BI dataset queries. There’s nothing out of the ordinary about the PBI dataset we’re using. It’s the OOTB one that come’s with CoE. You can find details about it here on docs.

Let’s look at the three scopes that have the Run a query against a dataset action. I’ve created separate actions for querying makers, apps, and flows as I don’t write DAX. If you do, then you might be able to write a DAX expression that queries everything in a single action. For this use case it doesn’t really matter whether we use multiple actions for querying or not. This is a background flow that doesn’t need to be fast and there aren’t throttling or Power Platform request limitations that would greatly impact the flow. If your use case has thousands of makers, apps and flows, then refactoring might be necessary.

Image 1 below shows the variable and the first query action.

A query action is followed by a compose, which hold the body of the query’s output for simplicity. After parsing JSON, the final action in each scope is a select. Properties are mapped from the firstTableRows array. For our example, I’ve selected and mapped the properties seen in table 1 below to create maker, app, and flow arrays. Note that the app and flow tables do not contain a value for a maker’s UPN (User Principal Name). That’s why the maker table is queried. Image 2 shows all three scopes and their actions, including the different dataset queries.

Listing a maker’s apps and flows

The next steps are where the real magic happens. After the initial queries, the maker array (output of the Select Maker action) is filtered to weed out items where the MakerUpn property is null. There are some apps and flows that are SYSTEM owned so there’s no need to loop though them. The condition used in the filter array action is @not(empty(item()?['MakerUpn'])). The filtered maker array is then looped through in an apply to each.

You might be wondering why the maker array is being looped through. The reason is that I’ve decided to append flows and apps to a maker instead of appending a maker to flows and apps. For me, this was an easier approach. Let’s dissect the apply to each and all the actions it contains. On a high level, this is what it does:

1. Filter the flow array to list all flows related to a maker being looped through, and append the results to the variable initialized at the beginning.
2. Filter the app array to list all apps related to a maker being looped through, and append the results to the variable initialized at the beginning.
3. Filter the variable array to which flows and apps were appended to.
4. Create and format an HTML table and send an email to a maker.

1. Filtering the flow array

The first filter array action that we’ll use is for filtering all flows where a flow’s MakerName is equal to the MakerName being looped through. This way we can return a maker’s flows in an array. The JSON of the filtered array is then parsed. The JSON schema might require some editing to make it work if there are null values. I recommend reading about parsing JSON by MVP John Liu here, and then figuring out the exact schema for the parse JSON action through a bit of trial and error. Next, the properties and values seen in table 2 below are appended to the variable we initialized at the beginning of the flow.

While I generally try to avoid nested apply to each loops due to the impact they have on performance, this use case is an exception where I’ll let nested loops slide. It would seem that cranking up degree of parallelism of the maker loop and all the nested loops in it doesn’t cause any issues appending to the array variable. With degree of parallelism (concurrency) se to maximum, the flow performs adequately, when considering it’s a background flow.

2. Filtering the app array

Next, the app array is filtered and the results are appended to the same variable we just added the flows to. At this point the variable holds items for a maker’s flows and apps. Filtering the app array is a rinse and repeat of the steps we just took with appending flows. As with the previous array step, the parse JSON’s schema might require some trial and error for the app array step as well. The properties and values seen in table 3 below are appended to the variable we initialized at the beginning of the flow.

3. Filtering the array variable

This is a step that’s easy to overlook! As we’re appending to an array variable, the variable has quite a bit of items in it when the maker loop hits its final iteration. We naturally want to send makers information about their own apps and flows, not about every single app and flow in the variable. This means that the array variable also needs to be filtered by matching the maker in the loop with the maker in the array variable. In other words, we’re interested in getting our hands on the apps and flows in the array variable for the maker we’re looping though. The following condition can be punched in the filter array action by editing it in advanced mode: @equals(item()?['MakerName'], items('Apply_to_each_Maker')?['MakerName']).

4. Creating and formatting an HTML table and sending it to a maker

The final step is to create an HTML table from the filtered array variable and to then format it. Unformatted tables look ugly but they can be formatted and beautified. This monster of an expression is something I regularly use in my blog posts, when I need to format HTML tables:

replace(replace(replace(body('Create_HTML_table'),
'<th>','<th style="text-align:center;color:white;background-color:#077D3F;padding:2px">'),
'<td>','<td style="text-align:center;color:green;padding:3px">'),
'<table>','<table style="border-collapse:collapse;width:100%">')

The formatted table is then sent out to the maker we’re looping though, as seen in image 7. The end result can be seen in image 8 as the formatted table. Project for the web green, of course!

That’s all folks! You can download an unmanaged solution containing the flow from our GitHub repo.