Delete push dataset via Power BI API programmatically with Python

[RolandBernhardt]

Hi Power BI community, I am trying to delete a push dataset programmatically in Python and will not use username and password but instead want to work with an access token. I do not get it successfully run and would need support.

What I have done:

1. I have registered Power BI as an application in Azure Active Directory with all rights following this documentation How to access Power BI REST APIs programmatically (sqlshack.com)

2. I wrote a python script which a.) requests an access token for the registered power bi app from AAD with App ID and App secret - that works and was successful. b.) tried to send a delete request to the respective Power BI API URI to delete the content of the push data set. 

I get the error response 401 - unauthorizes 

here is the python code which I am using  

"import adal
import requests

authority_url = 'https://login.microsoftonline.com/microsoft.onmicrosoft.com'

context = adal.AuthenticationContext(
authority_url,
validate_authority=True,
api_version=None
)

# request access token for registered Power BI app from Azure Active Deirectory
token = context.acquire_token_with_client_credentials(
resource='https://analysis.windows.net/powerbi/api',
client_id= <App ID from AAD>,
client_secret=<client secret from AAD>)

auth_token=token['accessToken']
header = {'Authorization': 'Bearer ' + auth_token}
urlpbi = "https://api.powerbi.com/v1.0/myorg/datasets/<your dataset-id>/tables/RealTimeData/rows"

# use access token to send delete request to push data set via Power BI API
response = requests.delete(urlpbi, headers=header)
print(response)"

I can clear the push dataset via PowerShell with out any problem but I need to login with username and password first - which is not a solution for a programmatically solution. 

- Connect-PowerBIServiceAccount

-  Invoke-PowerBIRestMethod -Method DELETE -Url "https://api.powerbi.com/v1.0/myorg/datasets/<dataset id>/tables/RealTimeData/rows"

Looking forward to your response and idea for a solution.

Best 

Roland 

[RolandBernhardt]

Hi Sheng (@v-shex-msft), sorry for my late reply, I thought I had send you already my feedback. 

When I use the "Try it" button, I have to enter my username and password and it works, as it work when I am using Powersehll - see my initial Post  "Connect-PowerBIServiceAccount" etc. 

Using username and password in an python skript would be unseruce. Best practice is to use an access token. Receiving an access token for registerred PowerBI App works, but the calling the PBI API with the delete push dataset fails with the access token. MAybe a bug in the API?

[Anonymous]

HI @RolandBernhardt,

As I said, your app is configured with 'master user' mode, and it does not support enabling both two modes at the same time.
For this scenario, I'd like to suggest you create a new app and configured it with 'service principal' mode, then your codes should work with the new authorization mode app.

Regards,

Xiaoxin Sheng

[RolandBernhardt]

Hi Sheng (@v-shex-msft), sorry for my late reply, I thought I had send the reply already.

When I use the "Try It" button, I have to login with my username and password and it works like it works when I use PowerShell "Connect-PowerBIServiceAccount" etc. But I do not want to use my username and password in the python code because it is unsecure. Best practive would be to request an access token and to call the API with the delete request which fails. Any idea or maybe it is a bug in the API?

[RolandBernhardt]

Hi Sheng (@v-shex-msft), it works with a response code 200 like it works when I am using PowerShell with "Connect-PowerBIServiceAccount" and "Invoke-PowerBIRestMethod -Method DELETE -Url "https://api.powerbi.com/v1.0/myorg/datasets/<dataset id>/tables/RealTimeData/rows". But I need to use my username and password to login. Using username and password in a programmatical way is from a security aspect not the best approach. Working with a App Secret and a requested Token would be the better approach.

Any idea why the approach via requested Token from AAD PowerBI App is not working?

[Anonymous · Accepted solution]

Hi @RolandBernhardt,

So you mean these operations work well in master user mode(account/password) but fail in the service principal mode? (client id/secret with the token)

If that is the case, I think you may need to register a new app with configurations permission that is suitable with service principal mode.

Embed Power BI content in an embedded analytics application with service principal and an applicatio...

In addition, you can also take a look at the following link about using python with the rest API which gets the same error code if help:

python - API Power BI Get token but get request get response 401 - Stack Overflow
Regards,
Xiaoxin Sheng

[RolandBernhardt]

Hi Sheng (@v-shex-msft), thank you so much for your support. I was trying the link Embed Power BI content in an embedded analytics application with service principal and an applicatio... and I can not  Enable the Power BI service admin settings because it seems, my admin rights are limited. So, I assume the issue is because of a limitation of my rights in the Power BI tenant. Thank you anyway and I realy appreciate the effort you have spend.

[Anonymous]

HI @RolandBernhardt,

You can take a look at the following link about using programming language to get access tokens to work with other rest API operations if it helps: (AFAIK, get access token operation requires the account and password)

Solved: REST API Get Access Token - Microsoft Power BI Community

BTW, the 'delete dataset' API required the 'Dataset.ReadWrite.All' permission, please check the corresponding account license and permission configuration first.

Datasets - Delete Dataset In Group - REST API (Power BI Power BI REST APIs) | Microsoft Docs

Power BI REST APIs for embedded analytics and automation - Power BI REST API | Microsoft Docs

You can also take a look at the below link about MSAL(Microsoft Authentication Library) with python if helps:

Home · AzureAD/microsoft-authentication-library-for-python Wiki · GitHub

Regards,

Xiaoxin Sheng

[RolandBernhardt]

Hi Sheng (@Anonymous), thank you for your response. I went through your links above but non really solve the issue.

- Get Token:  to get an access token for the registered Power BI App works in my Python code version and it is similar to the description you have posted. The diffence is, that I am using an app secret instead of a username and password

- the registered Power BI App in AAD has Dataset.ReadWrite.All rights delegated

Thank you also for the other links and I had read them before but could not find a solution for deleting a push dataset programmatically. 

So, I was putting all bits and bytes from available posts and docus together to come up with the solution approach above. But, when I call the delete dataset request with the access token for the Power Bi App, I receive a 401 error and do not know why.

[Anonymous]

Hi @RolandBernhardt,

What happens when you test with the current account that you used in python testing with the rest API online sandbox? (rest API document has a 'try it' button to open the online testing sandbox)
Regards,

Xiaoxin Sheng

[RolandBernhardt]

Hi Sheng (@v-shex-msft), sorry for my late reply, I thought I had send you already my feedback. 

When I use the "Try it" button, I have to enter my username and password and it works, as it work when I am using Powersehll - see my initial Post  "Connect-PowerBIServiceAccount" etc. 

Using username and password in an python skript would be unseruce. Best practice is to use an access token. Receiving an access token for registerred PowerBI App works, but the calling the PBI API with the delete push dataset fails with the access token. MAybe a bug in the API?