In Power BI there are plenty of features to allow people discover content, but I don’t think it’s really useful. For once, with the exception of promoted & certified datasets, you only find what you have access to, and in many places access is given on a one-by-one basis. You ask for it, you get it and and you go on with your life, but you don’t know what you don’t know, and that can be limiting. Sometimes information is not shared because no one has bothered to share it (maybe because they don’t have role-based security groups). What if we could show a list all the apps that are out there and you could search simultaneously in app name, app description, report name and report description? The link to the app allows you to request access. Sounds good? Let’s get started!
Let’s get the data!
As you might already imagine, we’ll need to play with the APIs. For this use case, in theory you could get away with just using the rest api’s directly in Power Query, because we are only interested in the latest info available. Unfortunately I’m not a Power Query wizard like Štěpán Rešl, and I also have a bit of a diogenes syndrome so I tend to put everything in a blog storage using the data factory I already have, so I can store daily snapshots of everything. So far nobody has complained and it has been handy in many occasions. If I want the latest data I just filter by maxDate on the dataflow and that’s it. Anyway, all of this to tell you that you can achieve the same using different approaches. As Vahid Doustimajd has shown, even Power Automate can be used.
One of the APIs that you’ll need of course is the GetAppsAsAdmin. The call itself does not allow almost any parameters. What I found out is that the $top parameter is supposed to be optional, but in practice is not. Anyway, it’s not limited, so put a big number there that will be enough to list all the apps in your organization. (For details on how I set up the data factory, please check out my previous governance post on the topic)
The API call will return a json that has a structure as follows (obfuscated a bit the guids):
{
"@odata.context": "http://wabi-west-europe-redirect.analysis.windows.net/v1.0/myorg/admin/$metadata#apps",
"value": [
{
"id": "564c1edb-df27-443f-8e6f-7*******",
"name": "Azure Backup",
"lastUpdate": "2019-09-09T14:30:56.396Z",
"description": "Gain insights and create custom reports for protected data assets to drive key business decisions",
"publishedBy": "Reports ",
"workspaceId": "52d9e211-3820-412b-b525-49b5d3e58cb8",
"users": []
},
{
"id": "b7703bfa-73f0-4080-91e8-******",
"name": "IT | Power BI Governance",
"lastUpdate": "2023-06-22T10:34:03.641Z",
"description": "Collection of reports relevant to understanding key metrics of activity and items shared on Power BI Service, as well as the licenses.",
"publishedBy": "BERNAT AGULLO",
"workspaceId": "5fdbd3fb-f493-4464-a444-*****",
"users": []
},
{
"id": "086e04bc-db6a-49eb-822a-0******",
"name": "IT | Centro de Información",
"lastUpdate": "2023-06-15T13:51:30.589Z",
"description": "Informes sobre el estado de los procesos del centro de información y las tablas del servidor de producción",
"publishedBy": "BERNAT AGULLO",
"workspaceId": "c4414f09-6756-4451-a1b7-******",
"users": []
}
]
}
So it has a first level with 2 items: @odata.context & value, and it is inside value where we find the array of Apps. This detail is important when we want to get more details about each of these apps. There is another admin APIs that we might want to look at GetAppUsersAsAdmin. Also there are other APIs which are not admin APIs that can return more detailed information about the App, but only if the user making the call has access to the app. The most relevant for our use case would be Apps – Get Reports. If dashboards in apps are a thing in your org there’s an API for that too. Both APIs require the App Id to be part of the call, so we need to iterate over the results that we got with the first API call.
This is worth discussing a bit more in detail because it drove me mad the first time I had to set it up. Data Factory expressions are terrible, and the documentation is not as good as one would wish. Anyway, let’s have a look at the Pipeline. This one is only «inspired» by Just Blindbæk’s work, not completely stolen like most of my data factory.
I’m even worse at Azure Data Factory than I am at Power Query, so bear with me. I’ve seen that in order to iterate over some values you cannot just grab the output of the «Copy Apps» activity, but you have to do a Lookup, which is the «Lookup Apps» activity. This brings the content of the JSON in the game so to say. However, the expression that you have to use in «For Each App» to really iterate over each app is not easy:
@activity('Lookup Apps').output.value[0].value
This is the expression. Why? Well, my conclusions are: To deal with the JSON contents, we need to use «output.value». However Data factory treats all JSON structures like arrays. But of course the array refers to the top level. If we want to iterate over the elements of one of the fields of this top element we need to say so. That’s why we add the [0]. We are saying «access the first element of the array» (which is the only element, but we need to say so anyway). Now that we are on that first element, where is my array? in the field called «value». That’s the second value of the expression. It almost makes sense once it’s explained right? Well, if it works, it works.
Inside the «For Each App» activity you can put as many Copy activities as you’d like. The one to copy the users of each app works fine, but the one to get the App Reports does not. It’s weird, with the same user and app it works from the API documentation page with the «try it» button, but it does not work from ADF. The reason might be related with the fact that you cannot use service principal for that call. So I’m using a generic user without MFA to do the authentication. It was not easy to get that approved. But even with that it’s not working. I have an open ticket with MS for that. Anyway, are we doomed?
I have found a different route to my goal. And that’s the Scanner API. Just has a few posts on the topic, this is the latest one that will bring you to all the others. One of the weird things that I found with the scanner APIs is that in the contents of the workspace it listed the reports, but also copies of those reports with «[App] » in the beginning. To make matters worse, in the activity log, if you consume a report through an app, the activity is logged with the original report id and not the id of the report with the «[App] » in front. There’s a «Consumption Method» column that tells you that the report was consumed through an app. So I really wondered what was the point of listing those… until this use case.
I know what you must be thinking…. «But Bernat what about the Audiences?! not everyone can see everything bla bla bla» Hey, I did not make the APIs and yes I wish that info was accessible through APIs too. But remember, we just want a list of reports for each app. The list of users & groups for each app can help bring a some extra context on who might be using each app. Once you request access, the admin of that workspace will decide which audience is the appropriate for you if he or she gives you access at all
Let’s build the Report!
We’ll talk a bit more about API’s later, but let’s start building our report (the App Portal), otherwise we’ll lose focus.
Load your App list, and your reports-in-app list. If you are lucky enough to get the ‘Apps – Get Reports’ API working, you’ll have the appId and the report Id together. If you have to go with the scanner API you will not get the app api for each report, but you do get the workspace Id which is just as fine since we always have only one app per workspace. Of course in this route you need to filter first only those reports with [App] in the beginning of the report name, and then remove that prefix so that they match their original name. No big deal.
Our modeling will be crude and inusual. PLEASE DO NOT USE FOR ANYTHING ELSE. I actually… yes. I created a BIDIRECTIONAL RELATIONSHIP 😱 to filter the App table from the report table. Probably there’s a different way, but hey, it’s just cosmetic ok, let’s not freak out. I want people to look through the report list and say «hey this looks interesting» then clicking it will filter the list of apps leaving the only one that contains it along with the link to access it. So yes, this is the model
Now, at the very least we need a way to bring the users to the actual Power BI App, so we need to build the link. This is not too complicated. Just opening an app we’ll see that the URL is quite straight forward, so our measure can be built like this:
App Link = VAR appId = SELECTEDVALUE ( Apps[Id] ) VAR link = IF ( appId = BLANK (), BLANK (), "https://app.fabric.microsoft.com/groups/me/apps/" & appId ) RETURN link
Now if you know you’ll have guest users using the app, you might want to go a bit more sophisticated and use the links that Power BI gives you when you publish the app or you go inside app configuration and select «Copy Link»
App Link = VAR appId = SELECTEDVALUE ( Apps[Id] ) VAR link = IF ( appId = BLANK (), BLANK (), "https://app.powerbi.com/Redirect?action=OpenApp&appId=" & appId & "&ctid=0179f751-ddf9-4e52-aad5-********" ) RETURN link
As you see the link now includes the «ctid» parameter which is the ID of your tenant. That makes that even guest users can use the link because it will bring them to the right tenant before looking for that particular appId.
Now you can create a table visual for apps with the link the app name and the app description, and a table visual for reports with the name and the description
This already brings some more information than the Power BI Service. We can see easily all the apps, click at an app and see the reports it contains, all along the descriptions of apps and reports. But can we do better? Of Course! we want to search right? Looking through a list of things is like looking an encyclopedia instead of searching on google. So how can we search…. on 4 fields simultaneously??
Well it’s not really elegant, but it works. Basically you concatenate them!
On the report table lets add a calculated column (today we’ll do all the sins) with this formula:
Filter = Reports[Report name] & " | " & Reports[Report Description] & " | " & RELATED ( Apps[Name] ) & " | " & RELATED ( Apps[Description] )
Now we’ll go to the App source to download a custom visual (I told you, all the sins). We’ll download «Text Filter» by Microsoft, but probably can be achieved with others too. We’ll add the search box on top of both tables and we’ll use the brand new column as its field.
And voilà!
it would be awesome if it searched as you type, but you need to click enter. Oh well.
Let’s bring it to the next level!
The report is ok, but there’s no joy. Let’s revisit the APIs for something that as of today, they do not provide. And that’s the ability to get the images of apps and reports. Both apps and reports allow to add an icon or snapshot to them. I favor making interfaces as graphical as possible and I think images help in this direction. Microsoft on the other hand is rowing in the opposite direction removing color from icons and hiding the icons of apps from the list view of apps where earlier we could see the icons.
Yes I know that the colors in icons will come back eventually. I wish that could be tomorrow, I hate these colorless icons. Anyway, the images of apps can be seen if you put the gallery view in the app section.
And what about the images of reports? Wait, you don’t know you can upload a snapshot of your report? Yes you can
The bad news is that, these are only used for that terrible thing that is the «Recommended» section that I’m sure no one ever uses. I don’t, that’s for sure.
I wish we could get all those images as links or as base64 images in the API calls (you too? please vote for this idea!). That way we could build a more engaging portal. Depending on the number of apps you have, though, it may be worth it to do some manual workaround.
Publish your report/portal and export the App table as a connected table in Excel.
Right click on the table, go to Table –> Edit Query and add the field (well you can also add the field «Id» on your report before that, but where’s the fun on that)
DEFINE VAR __DS0Core = SUMMARIZECOLUMNS ( 'Apps'[Name], 'Apps'[Description], 'Apps'[Id], "App_Link", 'Apps'[App Link] ) VAR __DS0BodyLimited = TOPN ( 500000, __DS0Core, 'Apps'[Description], 1, 'Apps'[Name], 1 ) EVALUATE __DS0BodyLimited ORDER BY 'Apps'[Description], 'Apps'[Name]
Now add a regular excel column called «App Icon» to the table
Then go to the gallery view of the apps, and right-click on the image and «Copy image link».
Now paste it in Image column of the row containing the app.
Once your are finished copy pasting links, put the excel in a oneDrive or SharePoint folder and add it as a data source. Join with the app table by app id and add the new image column to the table. Make sure to refresh the dataset before continuing
Now, it would be quite dangerous to leave it as it stands now, because when you refresh the query to get the new apps that have been published, there’s no guarantee that the links we pasted will stay consistent with the data from the query.
In order to avoid that, now that the App Icon is already part of the data source, we’ll modify the query to get that column from the source and remove the manual column! (you might need to adjust a bit your power query for reading the file after it)
DEFINE VAR __DS0Core = SUMMARIZECOLUMNS ( 'Apps'[Name], 'Apps'[Description], 'Apps'[Id], 'Apps'[App Icon], "App_Link", 'Apps'[App Link] ) VAR __DS0BodyLimited = TOPN ( 500000, __DS0Core, 'Apps'[Description], 1, 'Apps'[Name], 1 ) EVALUATE __DS0BodyLimited ORDER BY 'Apps'[Description], 'Apps'[Name]
This will ensure that our links to not get all mixed up when the app list refreshes. This technique is the same I already talked about in this other blog post of mine.
And then?
Well change the data category to Image URL and boom! beautiful App portal.
My hope is that this portal will encourage developers to write meaningful descriptions for apps and reports as they become much more visible. Also hopefully the app icons will improve (also the retrieval process!). And, maybe, one day, we’ll get the base64 images of the reports and we can have a truly engaging App portal that will finally bring apps to the center of content consumption.
This time I can’t share the pbix with you all, but hopefully you’ll be able to put it together.
Please follow the conversation on Twitter and LinkedIn
Regards!