As an organization becomes fonder of Power BI reports, numerous reports are built…and these assets start to pile up. Reports that aren’t being used end up sitting around collecting dust. It’s time to declutter to make space for your most essential Power BI reports.
Since you’re on clean-up duty, you’ll be happy to know that there is a handy report that will help you purge the reports you no longer need. This report provides a comprehensive look into all workspaces, groups, dashboards, and reports in your tenant.
Power BI REST API is a great tool to have in your tool belt.
We’ll cover two approaches for gathering data from your tenant—Power BI Custom Connector and Postman. A combination of the two will save you a great deal of time and effort if your organization has been consistently using Power BI Service.
Please note…I do have Power BI tenant admin privileges.
How Do I Call Power BI REST API From Custom Connector?
The quickest approach is a Custom Connector that uses Power BI REST API under the hood. This option allows you to create a report within minutes of installing the connector.
Follow Steps 1-8 if you’re using Custom Connector.
1. Find the Custom Connector here on The Power User Miguel Escobar’s Github page. You can download the .mez file or go directly to the link to download it.
2. If you have not used Custom Connector in the past, be sure to create the Custom Connector path shown below. This folder will be accessed by Power BI when you start it up again. Make sure you have the right folder names and the correct folder path…
3. Now when you open Power BI, you will be greeted with the warning shown below. As the warning states, you need to lower the Security option (selected in Options and Settings-> Security). You have to take this step because the connector is in the Beta release stage and is not yet certified.
4. If you are comfortable doing so—this depends on your organization’s security and compliance policies—head over to File-> Options and Settings -> Options.
5. Lower the Security settings for Data Extensions.
6. Restart Power BI, then you will be able to access the connector in Get Data.
7. With this connector, you can access your tenant’s reports and workspaces and load in all the data by only selecting the data queries you need.
8. And if the “Auto-detect new relationships when data is loaded” option is checked (within Options->Current File->Data Load), you should be able to create a quick measure to display the number of reports in each workspace:
Number of reports = COUNTROWS('Reports (Admin)')…that’s all there is to it with Custom Connector!
How Do I Call Power BI REST API From Postman?
At this point, you might be wondering why we are taking the time to talk about calling Power BI REST API from Postman since gathering data through the Custom Connector is so easy.
I suggest you familiarize yourself with the wide range of API calls and the capabilities they offer. The sheer number of calls—the information you gather and the actions you perform with them—will leave you wanting to have the capability to issue GET and POST requests.
Postman is an API platform for building and using APIs. Postman simplifies each step of the API lifecycle and streamlines collaboration so you create better APIs even faster.
Follow Steps 1-20 if you’re using Postman.
1. Register the App for the Client ID and Client Secret.
To use Postman to send requests, you need to register an App so that you can provide a Client ID and a Client Secret in the request (more on that later).
Technically, you can register an App here (Power BI App registration in Developer App registration tool). But since you will be led to the Azure portal to set permissions for the app, let’s move over to the Azure portal right away and register the App in Azure Active Directory.
Follow the series of screenshots below to Azure portal’s Azure Active Directory. You can register the app by providing:
- A name (your choice).
- A URL that is the endpoint for the app (this is NOT a “working” URL…you can make this up).
Note…If providing a localhost, your URL will start with http://localhost. For a Web URL, you begin with https://
2. Upon registering the App, you are handed an Application Client ID, Store this up for use in Postman requests.
3. For the App you just created, add a Client Secret that will be displayed as the Value (not the Secret ID) in the Client Secrets section. Copy that and keep it handy for Postman requests.
4. Delegate/assign permissions to this App to access data through API calls. Select API permissions and Request API permissions for Power BI Service. Within each of the options below, select the permissions you are willing to provide. I selected “read permissions for the App” (more on permissions and requests below).
5. Request permissions from the Azure Subscription Admin for the App. Once permissions are granted, the status shows a green tick.
6. At this point, you have the App and the necessary Client ID and Client Secret saved up and you can employ these requests in Postman. Now that you are ready to move over to Postman, download Postman here and deploy the client.
7. To set Postman up for use with various kinds of requests that you will be mailing off, set up Environment variables and Collections. (You can read up on Collections and Environment and other facilities in the documentation.)
Once you tee up a series of requests in Collections—and set up global variables with the authorization keys that need to be linked to every request in the Environment—you are free from the repetitive task of including these in every request header.
Variables allow you to store and reuse values in your requests and scripts. By storing a value in a variable, you can reference it throughout your collections, environments, and requests . And if you need to update the value, you only have to change it in one place. Using variables increases your ability to work efficiently and minimizes the likelihood of error.
The first request you will send will be a POST request, which needs to be accompanied by user and application IDs and passwords.
https://login.microsoftonline.com/common/oauth2/token8. To organize your Power BI REST API tasks, start with a new Workspace. Within the Workspace, in the left navbar in Postman, select Environment and create a new Environment. I named my environment “Power BI API Common Credentials.”
9. In this Environment, add the variables with the values as seen below. We will check that the values have been duly populated below.
VARIABLE INITIAL VALUE
username: The user name that you used to login to Azure portal
password: The password that you used to login to Azure portal
client_id: The application id (see Step 3 — registering the App above)
client_secret: Application client secret (see Step 4 — creating the client secret above)
temp_access_token: Leave blank (this will be populated by the response and will carry the bearer token)
Note: Current value will be duplicated over from the Initial value
10. Now let’s create a Collection that contains all of our Power BI REST API requests. I named mine “Power BI API request collection.”
11. The first request to send is a POST request with user and application credentials that will return a Bearer token, which will be the Authorization required to be sent with every subsequent API call.
Click on the “+” seen below to open a tab to send requests. Also check if the globals are set as desired by typing {{variable name}} and hovering over it as seen. You should see the Initial value you entered in the popup.
12. Add a request to the created collection and this request is a POST…
https://login.microsoftonline.com/common/oauth2/token
[Also place the below in the Body tab]
grant_type=password
&username={{username}}
&password={{password}}
&client_id={{client_id}}
&client_secret={{client_secret}}
&resource=https://analysis.windows.net/powerbi/api
13. To populate the temp_access_token we set up earlier in Step 11 (without having to copy it in!) with the Bearer token that will be returned as a response to this POST request, in the Tests tab place the following:
var token;
try {
var response = JSON.parse(responseBody);
token = response.access_token;
pm.environment.set("temp_access_token", token);
}
catch (err) {
console.warn(err.message);
}
14. One last entry for the Header tab…add the below key and SEND this request.
Key :Content-Type
Value : application/x-www-form-urlencoded
15. If the authorization and credentials are set and your Azure Admin has granted requisite permission to the App, you should see the response as seen above with the token_type “Bearer”.
You should also find that the temp_access_token in the global Environment variable’s Initial value is now populated with the access_token seen above. You can copy it over to the Current value if it has not already been.
16. This sets you up to send other API requests. For instance, let’s issue a GET request to cull the reports in My Workspace in the Service. You will need to fill in the API call…
https://api.powerbi.com/v1.0/myorg/reports
Under Headers add
Key : Authorization
Value: Bearer {{temp_access_token}}
[Check if the variable temp_access_token is populated by hovering over it. It should carry the value returned in access_token key of the response JSON]
17. The response JSON can now be saved into a file (see Save Response below) that can be read into a Power BI file with a JSON connector.
18. As an exercise, try out the API to get reports from a group. Think about the API call to get all the groups in the tenant so that you eventually use the group ID in the call…
https://api.powerbi.com/v1.0/myorg/groups/<group id>/reports19. Once you have the Workspace, Dashboard, dataset, Groups, and Workspace Users data using the rich set of REST API calls in Power BI, import the responses received and model your dataset by linking the IDs.
This comprehensive view of your tenant can then be used to hold discussions with users in the organization to set a strategy for clean-up and maintenance moving forward.
20. Finally, in the documentation for the Power BI REST API, you will find the permission required by the App to send the request. For instance, if you have identified the reports to be deleted, then to send the DELETE request, you will need the permissions granted to the App as seen below.
Last Piece of Advice
I’ll leave you with some quick troubleshooting advice. If you are anything like me, you have run into some trouble even though you followed all the steps dutifully.
The issues I ran across mainly revolved around authorization and credentialing—and global variables. Make sure the Initial and Current Value of all the credentialing global variables are correctly populated. For instance, check for extra spaces and accidental tabs.
Here are some great resources to check out:
SQL Shack
Chris Webb webinar on APIs
Data Veld
Carl de Souza
Still need help with Power BI REST API? Reach out to us and we’ll get you on the right track.
