topic Re: Access Power BI API with Python in Developer

Access Power BI API with Python

jb007 — Tue, 06 Jun 2017 19:17:30 GMT

My requirement is to push real time data into Power BI using Python to first read from a database and then send the data inside a Streaming dataset in Power BI.

The first thing I want is to make a simple "get" call to Power BI.

The official documentation explains the processes of connecting to Power BI via the REST API for either a Client App or a Web App. However, I'm using Python - not sure if that is either a client app or a web app.

Anyway, I am able to get the accessToken using the adal library and the method .acquire_token_with_client_credentials, which asks for authority_uri, tenant, client_id and client_secret (notice this is not asking for username and password). By the way, I've also tried getting the accessToken with .acquire_token_with_username_password, but that didn't work.

Unfortunately, when I use the below code with the obtained accessToken, I get a response 403.

#accessToken is received using the adal libary 
headers = {'Authorization': 'Bearer ' + accessToken, 'Content-Type': 'application/json'}
read_datasets = requests.get('https://api.powerbi.com/v1.0/myorg/datasets', headers=headers)
#shockingly, this will result in a response 403

After reading other stackoverflow posts and looking at console apps, I believe the reason this doesn't work is because there is no user sign-in process.

This thread mentions that using Client Credentials is not enough (it is enough to get the accessToken, but not enough to use the APIs)

Not sure how to proceed, but what I need is perhaps a way to keep using this adal template that gives me the accessToken, and also to provide my username and password (in a silent way, i.e. via the scrip, without GUI), and together with the accessToken, to access the APIs.

Re: Access Power BI API with Python

Eric_Zhang — Thu, 08 Jun 2017 01:39:35 GMT

@jb007 wrote:

My requirement is to push real time data into Power BI using Python to first read from a database and then send the data inside a Streaming dataset in Power BI.

The first thing I want is to make a simple "get" call to Power BI.

The official documentation explains the processes of connecting to Power BI via the REST API for either a Client App or a Web App. However, I'm using Python - not sure if that is either a client app or a web app.

Anyway, I am able to get the accessToken using the adal library and the method .acquire_token_with_client_credentials, which asks for authority_uri, tenant, client_id and client_secret (notice this is not asking for username and password). By the way, I've also tried getting the accessToken with .acquire_token_with_username_password, but that didn't work.

Unfortunately, when I use the below code with the obtained accessToken, I get a response 403.
#accessToken is received using the adal libary 
headers = {'Authorization': 'Bearer ' + accessToken, 'Content-Type': 'application/json'}
read_datasets = requests.get('https://api.powerbi.com/v1.0/myorg/datasets', headers=headers)
#shockingly, this will result in a response 403
After reading other stackoverflow posts and looking at console apps, I believe the reason this doesn't work is because there is no user sign-in process.

This thread mentions that using Client Credentials is not enough (it is enough to get the accessToken, but not enough to use the APIs)

Not sure how to proceed, but what I need is perhaps a way to keep using this adal template that gives me the accessToken, and also to provide my username and password (in a silent way, i.e. via the scrip, without GUI), and together with the accessToken, to access the APIs.

@jb007

It is a web app. After registion, Use the client_id and client_secrect to get the accesstoken. What are the client_id and client_secrect in your case? 403 in your case most probably indicates a invalid accesstoken, when dedcoding the token at http://jwt.io, what is the scp(scope)? Normally you'd get access as below according to the premission checked when registering the app.

Techinically it is possible to get the access token without GUI interaction in a silent way, just call the POST API with grant_type=password. Then use the refreshtoken to get token afterwards. Note that it might violate the license compliance if other people use the this token to access the embedded reports. People who access the reports in Power BI service shall have their own accounts/tokens.

However for the first time, AFAIK, you'll always have the GUI consent page and accept it.

Re: Access Power BI API with Python

kartikg2jee — Tue, 28 Nov 2017 11:13:01 GMT

HI , I am facing the same issue .

I am using to python script to use powerbi rest api.

1) First I regestered an app as a web app and noted its app id (to be used as client id)

2) I created a secret key for it

3) Gave it the required permissions

4) using the ADAL module , I was able to get the access token

5)But when I use the token in further get requests , I get the "403 : Forbidden" error , usually meaning that unauthorised access

Please help!!!

Re: Access Power BI API with Python

kartikg2jee — Tue, 28 Nov 2017 11:15:03 GMT

def sample_query():
    print("--------------inside sample query-----------------")
    
    headersData = { 'Authorization': 'Bearer'+tokenString ,
    'Content-Type': 'application/json',
     'User-Agent': "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/47.0.2526.106 Safari/537.36" }
    #urllib.request.urlopen
    
    request = urllib.request.Request('https://api.powerbi.com/v1.0/myorg/groups',headers=headersData)
    print("________________________________")
    print(request.get_full_url())
    print("________________________________")
    response_body = urllib.request.urlopen(request)
    
    print(response_body)

here is the query code after I get the auth tokken

Re: Access Power BI API with Python

adamwallace3 — Tue, 28 Nov 2017 17:53:04 GMT

This works for me.

Reporting Class: Using single user access with app owns data

class EmbedToken:
    def __init__(self, report_id, group_id, settings=None):
        self.username = 'USER'
        self.password = 'PASSWORD'
        self.client_id = 'CLIENT ID'
        self.report_id = report_id
        self.group_id = group_id
        if settings is None:
            self.settings = {'accessLevel': 'View', 'allowSaveAs': 'false'}
        else:
            self.settings = settings
        self.access_token = self.get_access_token()
        self.config = self.get_embed_token()

    def get_access_token(self):
        data = {
            'grant_type': 'password',
            'scope': 'openid',
            'resource': r'https://analysis.windows.net/powerbi/api',
            'client_id': self.client_id,
            'username': self.username,
            'password': self.password
        }
        response = requests.post('https://login.microsoftonline.com/common/oauth2/token', data=data)
        return response.json().get('access_token')

    def get_embed_token(self):
        dest = 'https://api.powerbi.com/v1.0/myorg/groups/' + self.group_id \
               + '/reports/' + self.report_id + '/GenerateToken'
        embed_url = 'https://app.powerbi.com/reportEmbed?reportId=' \
                    + self.report_id + '&groupId=' + self.group_id
        headers = {'Authorization': 'Bearer ' + self.access_token}
        response = requests.post(dest, data=self.settings, headers=headers)
        self.token = response.json().get('token')
        return {'token': self.token, 'embed_url': embed_url, 'report_id': self.report_id}

    def get_report(self):
        headers = {'Authorization': 'Bearer ' + self.token}
        dest = 'https://app.powerbi.com/reportEmbed?reportId=' + self.report_id + \
               '&amp;groupId=' + self.group_id
        response = requests.get(dest, data=self.settings, headers=headers)
        return response

Django View

def index(request):
... irrelevant Code...
    conf = EmbedToken(report.get('report_id'), report.get('group_id'))
    token = conf.get_embed_token()
    return render(request, 'unfi_breakout/unfi_breakout.html', {'selectedReport': token.get('report_id'),
                                                                'embedToken': token.get('token')})

HTML

{% extends extension %}
{% load staticfiles %}
{% block main %}

<div id="reportContainer" style="height: 100% ">
    <script src="{% static 'PowerBi/dist/powerbi.js' %}"></script>
    <script>
    var models = window['powerbi-client'].models;

    var embedConfiguration = {
        type: 'report',
        id: '{{ selectedReport }}',
        embedUrl: 'https://app.powerbi.com/reportEmbed',
        tokenType: models.TokenType.Embed,
        accessToken: '{{ embedToken }}',
        settings: {
              filterPaneEnabled: false
            }
    };

    var element = document.getElementById('reportContainer');
    var report = powerbi.embed(element, embedConfiguration);
    </script>
</div>
</div>

{% endblock %}

Re: Access Power BI API with Python

Anonymous — Fri, 13 Apr 2018 13:02:39 GMT

Hello
I want to reproduce this part on C# to manage row level security with python code.
Any help ?

GenerateTokenRequest generateTokenRequestParameters;
                    // This is how you create embed token with effective identities
                    if (!string.IsNullOrEmpty(username))
                    {
                        var rls = new EffectiveIdentity(username, new List<string> { report.DatasetId });
                        if (!string.IsNullOrWhiteSpace(roles))
                        {
                            var rolesList = new List<string>();
                            rolesList.AddRange(roles.Split(','));
                            rls.Roles = rolesList;
                        }
                        // Generate Embed Token with effective identities.
                        generateTokenRequestParameters = new GenerateTokenRequest(accessLevel: "view", identities: new List<EffectiveIdentity> { rls });
                    }
                    else
                    {
                        // Generate Embed Token for reports without effective identities.
                        generateTokenRequestParameters = new GenerateTokenRequest(accessLevel: "view");
                    }

Re: Access Power BI API with Python

zotai — Thu, 03 May 2018 08:39:38 GMT

This might be helpful for anyone visiting; I have started working on a python client for the PowerBI API, feel free to use it!

https://github.com/cmberryau/pypowerbi

Right now I have added the functionality I require, but I'll be continuing to develop it as I'm now working full time on a python back-end project using PowerBI.

So far, ive added the functionality to:

- Use a standard ADAL token for authentication

- Pull reports

- Clone reports

- Delete reports

- Rebind reports

- Embed reports

- Pull datasets

- Push datasets

- Delete datasets

- Push rows

Re: Access Power BI API with Python

Anonymous — Sat, 09 Mar 2019 06:50:54 GMT

Hi @Eric_Zhang

Can you please give a code example on this part?: "Techinically it is possible to get the access token without GUI interaction in a silent way, just call the POST API with grant_type=password. Then use the refreshtoken to get token afterwards."

I am close to desperation here. My code is below and the token request returns this:

Get Token request returned http error: 400 and server response: {"error":"invalid_grant","error_description":"AADSTS65001:
 The user or administrator has not consented to use the application with ID '6d6c4a8d-3560-4e8f-b705-116bc57d7b99' named 'PBI_API'.
 Send an interactive authorization request for this user and resource.\r\nTrace ID: b2d3934d-196f-43d5-ab43-a59a31c01500\r\nCorrelation ID:
 e124c31f-91c6-4c00-8ab8-38dcbeb91ab2\r\nTimestamp: 2019-03-09 05:39:46Z","error_codes":[65001],"timestamp"
:"2019-03-09 05:39:46Z","trace_id":"b2d3934d-196f-43d5-ab43-a59a31c01500","correlation_id":"e124c31f-91c6-4c00-8ab8-38dcbeb91ab2",
"suberror":"consent_required"}

First, I tried get_token_with_credentials which was successful however I could not send any requests with this token as it provides insufficient access, the issue was discussed here:

https://stackoverflow.com/questions/32341877/cant-get-client-credentials-access-token-to-authorize-power-bi?rq=1

so in request_token.py in site_packages/adal folder I added the below (a colleague of mine said it worked for him) :

oauth_parameters[OAUTH2_PARAMETERS.CLIENT_SECRET] = "hardcoded_clientSecret"

to the following functions:

def _get_token_username_password_managed(self, username, password):
def _perform_wstrust_assertion_oauth_exchange(self, wstrust_response):

import adal
import requests

parmeters = {
"resource": "https://analysis.windows.net/powerbi/api",
"tenant" : "xxxxx.onmicrosoft.com",
"authorityHostUrl" : "https://login.windows.net",
"clientId" : "6d6c4a8d-3560-4e8f-b705-116bc57d7b99",
"clientSecret" : "xxxxxxxxxx",
"username" : "xxxxxxxx@xxxxxx.com"
}

authority_url = (parmeters['authorityHostUrl'] + '/' +
parmeters['tenant'])
GRAPH_RESOURCE = '00000002-0000-0000-c000-000000000000'
RESOURCE = parmeters.get('resource', GRAPH_RESOURCE)

context = adal.AuthenticationContext(
authority_url, validate_authority=True,
)
token = context.acquire_token_with_username_password(RESOURCE,
parmeters['username'],'my_password',parmeters['clientId'])

accessToken = token['accessToken']

the task I am trying to achieve is to push dataset refreshes once underlying data is updated, but so far I couldn't properly authincate:

headers = {'Authorization': 'Bearer ' + accessToken, 'Content-Type': 'application/json'}
requests.post(f'https://api.powerbi.com/v1.0/myorg/groups/{group_id}/datasets/{dataset_id}/refreshes',headers = headers)

Re: Access Power BI API with Python

Anonymous — Sun, 10 Mar 2019 04:56:02 GMT

Ok, I am now able to answer this myself. What I needed to do is to go to App Registration in AD and press Grant permissions.

Was quite surprised nobody else had this issue.

Re: Access Power BI API with Python

Anonymous — Tue, 12 Nov 2019 16:04:58 GMT

Hi, I really like the look of this. Does it enable you to copy a dataset from one workspace to another?

Thanks

Ricky

Re: Access Power BI API with Python

hprasanth — Fri, 06 Mar 2020 05:35:18 GMT

In this code view.py EmbedToken(report.get() is not defined ??? what is the report attribute in class.please reply thank in advance