In this blog post I show the final part which is how I created the Power BI report which takes the previous 3 steps and then creates the Power BI Report.

I am going to show you how I got the data in using Power Query and then created the Power BI report.

The first part in the series can be found here: Which Power BI Reports do I have access to? – Part 1 – Using the power BI Scanner to get App Workspace data. – Data Analytics | FourMoo and Power BI

The second part of the series can be found here: Which Power BI Reports do I have access to? – Part 2 – Using the Power BI Rest APIs for Apps and Apps Users data – Data Analytics | FourMoo and Power BI

Getting the data using Power Query

The first thing I did was to start off with Power BI template file for the API Scanner. You can find the file here: Scanner API Tenant Metadata – PBI Tips.pbit

I opened the PBIT and loaded my data so that I got it all.

What I then did was I deleted everything except the table called “workspacesbl”, which contains all the workspace information.

This is what the workspacesbl looked like below

Next, I had to get all the reports from the Workspaces which I did below in the table called “reports”, where you can see the output below.

Following on from that I then needed to get report users or report access in the table called “report access where you can see the output below.

Next was to start using the information I got from the PowerShell data I had uploaded to OneDrive Earlier.

The first one I did was to get a list of the App Users, which is in my table called “App Users” as shown below.

This table gives me a list of all the users and which app the users belong to.

Next, I then got a list of the App Names from the App list I had used previously. This allowed me to be able to later relate the App GUID to the App name

This is what my table looked like below called “App Name”

Next, I had to then get a list of all the reports in the App.

This would allow me to then map the App Names to the App reports. This is what it looked like below.

I should also let you know that I am going to use the web url which will allow me later to create a link directly to the App and associated report.

Finally, from all the above tables I create a single table called “Reports for Users”, and this table contains all the data from the Apps and Workspaces. This allows me to be able to get all the required data into a single table.

Creating the Power BI Report

Now that I have my data, I can finally create the Power BI Report.

What I wanted to do was to create a measure which would filter to show only the reports for the currently logged in user. Yes I could use Row Level Security but I did not want to do this because of the additional administrative overhead.

Here is the measure that I created below

Is Current User =

VAR MyUserName =

USERPRINCIPALNAME ()

VAR CheckIfMe =

IF ( MAX ( ‘Reports for Users'[Email Address] ) = MyUserName, 1, 0 )

RETURN

CheckIfMe

What this measure does is it first gets the user who has logged in with the USERPRINCIPALNAME

Then I check if the user who is logged in matches the rows in the final table ‘Reports for Users’ then give the result of 1, otherwise 0.

This measure is then used as a filter in my table to only show the logged in user.

I then uploaded this report to the Power BI Service, and I could view all the reports that I have access to in both Workspaces and in Apps

Yes, I did create another measure which is a greeting for the user, which is good for troubleshooting if there are any errors or issues.

I also used the Report Link column I created in my Power Query tables, which contains the link directly to the report in either the Workspace or App.

Summary

Thanks for reading I hope that you found this series interesting and useful.

I have found that this report is really useful for users to be able to quickly find the reports. Not only that it also helps them view all the reports that they have been granted access to.

You can download the Power BI Template file here: Power BI Reports List.pbit

If there are any questions or comments, please let me know.