Power BI REST API response 401

[Vinnie]

Hi all,

Hope you can help me with the following. I am trying to get a Python program to work which uses the Power BI API.

In Microsoft Entra ID I created an App. I have the client-id and client-secret available. It looks like this app has all permissions:

This is my Python code:

import requests
import msal
import pandas as pd
from powerbiclient import Report

# Azure AD-applicatie gegevens
client_id = 'x'
client_secret = 'x'
tenant_id = 'x'
groupId = 'x'
reportId = 'x'

# Authenticate with MSAL
authority_url = f'https://login.microsoftonline.com/{tenant_id}'
scopes = ['https://analysis.windows.net/powerbi/api/.default']

client = msal.ConfidentialClientApplication(client_id, authority=authority_url, client_credential=client_secret)
acquire_tokens_result = client.acquire_token_for_client(scopes=scopes) 
if 'error' in acquire_tokens_result:
  print("Error: " + acquire_tokens_result['error'])
  print("Description: " + acquire_tokens_result['error_description'])
else:
  print("Access token:\n")
  print(acquire_tokens_result['access_token'])
  print("\nRefresh token:\n")

access_token = acquire_tokens_result['access_token']

headers={"Authorization": f'Bearer ' + access_token}

url = 'https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports'

response = requests.get(url, headers=headers)

print(response)

The response is 401 (unauthorized). What am I doing wrong?

By the way, in the end I would like to use the Power BI API to enable us to make quick exports of visuals in existing Power BI reports to .csv.

[mattiasdesmet]

It should be a v2 workspace, but I believe all workspaces have been converted to v2 now.

The app registration should be available to select as a member to the workspace when you press "manage access" in the workspace.

[Vinnie]

Hi @mattiasdesmet , thank you for your help. I did a little more digging and contacted my support department. It seems that I didn't have the proper rights within the Power BI tenant. All 4 bullets are now enabled. That means that the app is now a workspace admin. And the two settings in the Power BI tenant settings are now enabled. It also seems that the app needed to be verified by my Azure tenant admin. When it was verified, I could add it to the workspace as admin.

When running my code now, I am getting a <Response [200]>, but the response is empty for the rest. Using my groupid of the workspace I added the app to as admin. I don't get this because this API call should return all reports which are in the workspace?

https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports

[mattiasdesmet]

That seems to be configured correctly and a Reponse 200 also indicates Status OK from the rest call.

The best way to check if this is something with the API or the python script to me is to try this out in Postman. One request to get the token and another to run the rest call.

The body should now contain a result like (which I just tested with the call /reports you mentioned)

{

    "@odata.context": "http://wabi-north-europe-g-primary-redirect.analysis.windows.net/v1.0/myorg/groups/cxxxxx/$metadata#...",

    "value": [

        {

            "id": "xxxxxx",

            "reportType": "PowerBIReport",

            "name": "Trace",

            "webUrl": "https://app.powerbi.com/groups/xxxx/reports/xxxxx",

            "embedUrl": ".....",

            "isFromPbix": true,

            "isOwnedByMe": true,

            "datasetId": "xxxxx",

            "datasetWorkspaceId": "xxxxx",

            "users": [],

            "subscriptions": []

        },

        ....

[Vinnie]

Hi @mattiasdesmet , this is now working. I forgot the 'f' at this line:

url = f'https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports'

It didn't send the groupid to the API call.

Thank you so much for your help. It is working now! I get the proper response.

[mattiasdesmet · Accepted solution]

Hi,

the app registration does not need any permissions.

- To retrieve the token, you'd use the URL https://login.microsoftonline.com/{tenant ID}/oauth2/v2.0/token

- Did you add the app registration as workspace admin member in the workspace you specify?

- Is the app registration (service principal) allowed to connect in PowerBI tenant setting "Allow Service Principals to use PowerBI API "

- Also check that tenant setting "Dataset Execute Queries REST API" is enabled

I wrote just a similar article about it here, using the PowerBI API from ADF (but works teh same for Python, Postman, or other client) : Use Power BI APIs from Azure Data Factory - Mattias De Smet

[Vinnie]

Hi @mattiasdesmet , thank you for your reply. This is, I think, very helpful.

At this moment, I do not have access to the Power BI tenant settings. So bullet 3 & 4 will have to wait till I have the proper rights.

Regarding bullet 2, how do I add the app registration as workspace admin? I can only add users to the workspace, but no app registrations?

Thank you again!