Problem authenticating to Power BI REST API with Python

Question

I've created a push streaming dataset (history on) and I've managed to post data to it from a Python script using the "Push URL" which I got from the API Info tab for the dataset in question. What I also need to do is to delete the historic data so as to clear out my test data and/or be able to reset the dataset and re-populate from scratch as and when necessary.

The Push Url is of the form https://api.powerbi.com/beta/xxxxxxxx/datasets/xxxxxxxxxxxx/rows?key=xxxxxxxxxxxxxxx

The following code works fine and the data is posted;

import requests 
import pyodbc as db
import pandas as pd

API_ENDPOINT = "https://api.powerbi.com/beta/xxxxxxxx/datasets/xxxxxxxxxxxx/rows?key=xxxxxxxxxxxxxxx"

dbcon = db.connect('DRIVER={SQL Server};SERVER=tcp:fxdb.database.windows.net;DATABASE=FXDatabase;UID=xxxx;PWD=xxxx')
df = pd.read_sql("select statement etc...", dbcon)
data = df.to_dict(orient='records')

response = requests.post(API_ENDPOINT, json=data)

But adding this:

response = requests.delete(API_ENDPOINT)

gives me:

404

{
  "error":{
    "code":"","message":"No HTTP resource was found that matches the request URI 'http://api.powerbi.com/beta/...

I couldn't figure this out so I started looking into OAuth2 authentication thinking that perhaps the Auth URL is only for posting data. After registering the app at https://dev.powerbi.com/apps my code now looks like this:

import requests 
import pyodbc as db
import pandas as pd

API_ENDPOINT = "https://api.powerbi.com/beta/xxxxxxxxxxxxxx/datasets/xxxxxxxxxxxxxxx/rows"

data = {
        'grant_type': 'password',
        'scope': 'openid',
        'resource': r'https://analysis.windows.net/powerbi/api',
        'client_id': 'xxxxxxxxx',
        'username': 'xxxxxxxxx',
        'password': 'xxxxxxxx'
    }
response = requests.post('https://login.microsoftonline.com/common/oauth2/token', data=data)

access_token = response.json().get('access_token')
headers = {'Authorization': 'Bearer ' + access_token}

dbcon = db.connect('DRIVER={SQL Server};SERVER=tcp:fxdb.database.windows.net;DATABASE=FXDatabase;UID=xxxx;PWD=xxxx')
df = pd.read_sql("select statement etc...", dbcon)
data = df.to_dict(orient='records')

response = requests.post(API_ENDPOINT, json=data, headers=headers)

response = requests.delete(API_ENDPOINT, headers=headers)

The authentication works, returning status code 200. The POST returns 401 (this worked with the previous method) and the DELETE still returns 404.

Have you looked into the API docs to find out what endpoints and methods are supported? If it was an authentication problem you'd get 401 or 403, not 404. That said I'd expect 405 from an endpoint that exists but doesn't support the specific method, so the API may not be particularly well formed. — jonrsharpe
– jonrsharpe, Commented Jan 6, 2019 at 17:02
You may be on the right track Jon as the authentication post does return success. The endpoints are the same though; DELETE api.powerbi.com/v1.0/myorg/datasets{datasetKey}/tables/{tableName}/rows and POST api.powerbi.com/v1.0/myorg/datasets{datasetKey}/tables/{tableName}/rows. They might both be wrong of course but I have tried a couple of variations to no success. Hoping someone with experience of these particular API calls might have some working examples. — David Fitz-Gerald
– David Fitz-Gerald, Commented Jan 6, 2019 at 17:22
I should add that I structured the enpoint urls based on the original "Push url" but removing the "key=" part. The urls in the documentation are slightly different and mention {tablename}. I don't know where to get this piece of information from though so if anyone knows where I can find it I can try updating the endpoint urls accordingly. — David Fitz-Gerald
– David Fitz-Gerald, Commented Jan 6, 2019 at 17:31

David Fitz-Gerald · Accepted Answer · 2019-01-06 18:00:32Z

1

Thanks to jonrsharpe who pointed me in the right direction.

Revisiting the API documentation I discovered a call to get the table names;

GET https://api.powerbi.com/v1.0/myorg/datasets/{datasetKey}/tables

so after authenticating I ran;

response = requests.get("https://api.powerbi.com/v1.0/myorg/datasets/xxxxxxxx/tables", headers=headers)

The content of the response told me that there was a table called "RealTimeData" inside my dataset, must be a default name because I haven't knowingly created this table.

I have now updated the endpoint to;

API_ENDPOINT = "https://api.powerbi.com/v1.0/myorg/datasets/xxxxxxxxx/tables/RealTimeData/rows"

and all works perfectly.

Thanks Jon!

answered Jan 6, 2019 at 18:00

David Fitz-Gerald

314 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Problem authenticating to Power BI REST API with Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related