Connecting to dataverse from PowerBI using client id , client secret

[Harineeeeeeee]

Hi ,

I am trying to connect to dataverse from PowerBI, I have only this info tenant id, client id, client secret and env url based on the info I have wrote a power query as below, but this is not giving the desired dataverse tables

rather data format error : we found extra characters at the end of the json output I want the output in tables in PowerBI 

let

Tenantid =“xx",
ClientID = "xx”
ClientSecret = “xx",
Authority = *https://login.microsoftonline.com/"& TenantID &"/oauth2/token*
headers = [#"Accept"="application/json*, #"Content-Type"="application/x-www-form-urlencoded*],
postBody = [grant_type ="client_credentials"
Client_id = “xx”,
client_secret = "xxx”,
#"Converted to Table" = Record. ToTable(postBody),
response = Json. Document (Web. Contents (Authority))
In
response

can some please assist?

thanks in advance

[Anonymous]

Hi @Harineeeeeeee 

Maybe you can create a dataflow in power bi service to connect to Dataverse:

Then you can use service principal to authenticate:

So that you can get data from dataflow in power bi desktop:

Here for your reference:

Create your first Microsoft Fabric dataflow - Microsoft Fabric | Microsoft Learn

Creating a dataflow - Power BI | Microsoft Learn

Hope it helps.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Harineeeeeeee]

@Anonymous  Hi thank you for suggesting this when I tried approaching in the dataflow it says invalid credentials should I need a also configure a gateway for this? Will that help ?

bcoz my cress are right !

[Anonymous · Accepted solution]

Hi @Harineeeeeeee 

You need to add the App registrations to the Security group and add the security to the Dataverse environment, so that you can use the security principal to authenticate.

Here for your reference:

Control user access to environments: security groups and licenses - Power Platform | Microsoft Learn

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Harineeeeeeee]

Hey @Anonymous  thanks for this, it worked but when I import the dataflows to local desktop the tables are empty! 

[Anonymous]

Hi @Harineeeeeeee 

Please check if there are any data in your dataflow.

After you've created your dataflow, you'll need to wait for it to refresh before you can connect.

In general, this may continue for a while

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Why not use the standard connector?

[Harineeeeeeee]

Hi @lbendlin  the standard connector allows only organisational account as credentials I don't have option to tie out with service account , also I have this info client id, client name , tenant id and client secret