Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I am a Power BI admin and i am trying to create a governance report for my Power BI tenent. I am using the Admin REST API but the token gets expired after about an hour. Can someone help me know how i can get a refreshable token which i can use in my report?
Hi @Anonymous,
I have added your code to my query, but it is not working. The error is:
--> PowerBI DataSource.Error: Web.Contents failed to get contents (400): Bad request
What could be the problem?
() =>
let
body = "grant_type=client_credentials"
& "client_id=XXXX"
& "client_secret=XXXX"
& "resource=http://analysis.windows.net/powerbi/api",
Data = Json.Document(Web.Contents("https://login.microsoftonline.com/XXXX/oauth2/token/", [Headers=[#"Content-Type"="application/x-www-form-urlencoded"],
Content=Text.ToBinary(body)])),
access_token = Data[access_token]
in
access_token
It wont work, you will have to create a app and then create a secret key, the app can be created using the App management in Azure portal and the secret too for the app. That Secret and app will have to be used in "client_id=XXXX" and "client_secret=XXXX" respectively. You will need help from your Office admin to obtain these two.
Hi @Anonymous
I have created an app in Azure, so I did fill in the secret key and app id in MQuery, but replaced them with XXXX for security reasons. I also added the app registration to a Azure security group and added the security group to the workspaces. So why is this not working?
Find it very strange that there are so many people that are not able to use the PowerBI Rest API because of this. This should not be that hard right?
Go to your Power BI Admin portal and under "Allow service principals to use read-only admin APIs" select "Specific Security Group" and under that mention your APP name there and then try again.
Hi @Anonymous already did that too, and just to be sure did the same for: 'Allow service principals to use Power BI APIs'
Yes thats correct, basically that SPN or the app name you created needs to be in a Ad security group and that security group needs to go in that setting(only if you are enableling for security group).
Anyways give me some time i will share with you the M code which i am using and also the full step by step guide.
That would be great! thanks in advance @Anonymous !
Try this, create a function in PowerQuery using the code below, this will be used as a refreshable token.
() =>
let
body = "grant_type=client_credentials&resource=https://analysis.windows.net/powerbi/api&client_id=(mentioned your clientID/appid here)&client_secret=(mentioned your secret key)",
Data=Json.Document(Web.Contents("https://login.microsoftonline.com/2c93d3cb-f285-49c4-8265-fba1bd1fe468/oauth2/token/", [Headers=[#"Content-Type"="application/x-www-form-urlencoded"],Content=Text.ToBinary(body)])),
access_token = Data[access_token]
in
access_token
Hi @Anonymous
The function it self works, but the refreshable token is about half in lenght shorter than the one I receive from https://learn.microsoft.com/en-us/rest/api/power-bi/admin/groups-get-groups-as-admin?tryIt=true&source=docs#code-try-0 and I still get the same error as before: "We couldn't authenticate with the credentials provided. Please try again"..
I have the same thing happening with the lenght shorter. Did you ever find a solution for this?
Thanks.
Firstly, from the error it shows that there is no error in the syntex. Before posting the response here last week i had tested and refreshed it to make sure its still working and it worked fine. I am a little doubtful if you are either using the correct appid (which is the username) and secretkey (which is used as password) or the one you are using has not expired, because you choose an expiry when you create it. Can you clear the datasource credentials and start with a new workbook also i hope you have placed this appid in security group in the tenant settings .
Hi DataUpperCut,
Thank you for your patience..really appreciated!
The appid used is the one in yellow..
and the secretkey i use see below (i renewed it today just to be sure)
and this is the security group i have added to all workspaces:
Is this correct?
Did you add this secrutitygroup in the Power BI tenant setting under
"Allow service principals to use read-only admin APIs" select "Specific Security Group"?
Yes I did that also, see image below.
All right, lets do this then. Watch the video below (part 1 and 2) and see if you missed anything while registring app and then giving the right access to the app (this part is usually done by the admin of O365)
https://www.youtube.com/results?search_query=power+bi+custom+admin+usage+
Can someone help?
Hi @Anonymous ,
I think you can try to add the 'get access token' steps into your query table then use variable to store the generated token for further operation instead of use static token strings. (these token string will be re-invoked/generated every time the query refreshed)
let
//get token
url = "https://login.microsoftonline.com/xxxxxxx/oauth2/token",
GetJson =
Web.Contents(
url,
[
Headers = [
Accept = "application/json",
ContentType = "application/x-www-form-urlencoded"
],
Content =
Text.ToBinary(
"grant_type=client_credentials&
client_id=xxxxxxx&
client_secret=xxxxxxx&
scope=xxxxxx"
)
]
),
token = Json.Document(GetJson)[access_token],
//other api usage wiht above token
Result =
Web.Contents(
"https://xxxxx.xxx.com",
[
Headers = [
#"Content-Type" = "application/json",
Authorization = "Bearer " + token,
RelativePath = "/xxxxx/xxxxx"
]
]
)
in
Result
Regards,
Xiaoxin Sheng
Hi,
I just been working on accessing APIs in my Power Desktop report without much success. However the above script has been the first one which has been the only one which has been able return the token when I invoke the function - as below -
Here is the function which I amended as I called the function in a separate query
() =>
let
//get token
url = "https://login.microsoftonline.com/*********/oauth2/token",
GetJson =
Web.Contents(
url,
[
Headers = [
Accept = "application/json",
ContentType = "application/x-www-form-urlencoded"
],
Content =
Text.ToBinary(
"grant_type=client_credentials&
client_id=*************
client_secret=**************
)
]
),
access_token = Json.Document(GetJson)[access_token]
in
access_token
I have called the function in my query using
however the query still says "Access to the resource is forbidden" so it does not seem to be calling the function to accessing the token correctly.
Is there something that I am missing.
Thanks
Karen
Hi
I have now posted elsewhere for this
https://community.powerbi.com/t5/Desktop/Refreshable-Token-for-Power-Bi-Desktop/m-p/3152349#M1065034
But I can see that this is an issue with authorisation - so will update the other post.
Thanks
Karen
HI @Anonymous,
The bold part should be the personal token, id, and secret that the rest api requires which register and generated during the prerequisite steps and you can find them on the azure side:
The above are M query codes, you can use them on power query side. If you are not working with power query environment, you can take a look at the following link about using C# to get access tokens.
Solved: REST API Get Access Token - Microsoft Power BI Community
You can modify the function to store the 'get token' result into variables and check the expiry date before you use other APIs. (add if statement to check the expired date to choose to use an available token or invoke methods to generate new tokens)
Regards,
Xiaoxin Sheng
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |