I managed to get it working through application only: Requirements: 1) Power BI Service Admin 2) Someone enabled to create apps and security groups in Azure Portal How to: 1) Create an App in Azure, get its ID. 2) Create an App secret for you APP, get its value ID (not the secret ID). 3) Give you app all the readonly permissions to the power bi service (may work without the permissions). 3) Create a Security Group in Azure then add the app as a member. 4) Enable in power bi admin portal the following options for your security group: "Allow service principals to use Power BI APIs" "Allow service principals to use admin readonly APIs" OBS: I don't use the power bi service in english so the text might be a little different. 5) Add your security group as admin (might work with other roles) in every workspace. (The app won't be able to see a workspace it doesnt' have access) 6) Intead of using the 'password' grant_type use 'client_credentials', no need to send the 'username' and 'password' in the request but it's necessary to send 'client_id' (with value app ID from step 1) and 'client_secret' (with value app secret ID from step 2). Function 'GET Access Token' will look like this: () => let body = "client_id=step1_ID" & "&client_secret=step2_ID" & "&grant_type=client_credentials" & "&resource=analysis.windows.net/powerbi/api" , Data = Json.Document(Web.Contents("login.microsoftonline.com/your_tenant_ID/oauth2/token", [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(body)])), access_token = Data[access_token] in access_token OBS: The access you give to in the workspaces to the security group may take a few minutes to get caught by the API That's it! :)

OMG thank you! I have been looking for this EVERYWHERE!

Thankyou parker, I am not the powerBI pro but this is exactly I needed for the task in hand.

Thank you the video. It works great!

Eager for the next video in the series.. :) Thanks Parker for another fantastic video..

What a great lesson! Congratz!

u r my hero, thx buddy

Really thank u sooo much, I was searching this in almost website, now my work successfuly done

This is such an amazing video. Thousands of thanks!! :)

Ultimate stuff!! Really helpful!

Hi Parker! Really Great Job Thx!!!!!

Expand is only if you are admin. Looping is still good if you're not. Thanks a alot!

Can't wait to see part 3.... Still coming I hope?

You are awesome man ;)

Hey Parker - as always, great Playlist covering a popular topic! Do you have any advice/considerations how this can be handled when the user context has MFA enforced at a tenant level?

This is a excellent video. Helped me recreate this Admin view effortlessly. I like how you used expand instead of Mulitple API calls. Our tenant has more than 5000 Groups/Workspaces and the API's max limit is 5000. How can we include /circumvent this scenario.

Enjoying the series so far. The issue with using the $expand method is that it doesn't give you all of the information around a content type. For instance $expand for Reports doesn't give you these columns: embedurl, weburl, modifiedby, createdby. Anyway around that rather than having to use the method detailed in part 1?

When will the third part come out?

Is it possible to download the PBIX you put together for this series?

Can I add the Data-sources to the expand parameter or do I have to loop like the previous video? loop every datasets with GetDatasourcesAsAdmin would work but will be many request´s.

This is amazing stuff. Is there a way to bypass MFA?

Hi Parker, great content. I've recently been exposed to API calls and I've experienced what you are doing in both Power Query and Power Automate. However, I also came across API's, mainly used in Government departments, which use an API protocol called SOAP which I just haven't been able to crack. I can't even get Postman to connect. Any suggestions?

I am using the client credential grant type. For the government website. What was the resource URL for me? is the same as the token url? and I have the client id, subscription key, and secret.

Hello I know if is an older video but it is brand new to me. I am look to see if it possible to use the Api secret vs a user ID and password?

Hi, I am trying to create the dynamic refresh token, but I am not able to get the admin consent for using Tenant API, is there any other workaround to achieve this. your suggestions will be appreciated Thank you

Is there a way to get organization-level information about applications?

my code is working in postman but it gives response 400 in power query. Please advise

Hello Parker, Thank you for the video. I am not the admin of Power BI but my admin has registered an application with some permissions other than admin, gave me the client id. I would like to get the refreshable token. But, I am getting '407' error. Could you tell me what could it be?

Does this solution work if I publish this report to Power BI Service and schedule automatic refresh?

Hi - I had this all working previously but it seem sthat now the api url requires MFA anyone know who to make this work with MFA?

Hi while creating the Access token function its asking for credentials. How can we handle that?

Hi parker how can you refresh it in power bi service? Security doesnt allow for dynamic auth

love the video, but having to type the code from the video leads to errors. Can you put a link to the resource? I couldn't find part 2 on your blog. In Workspaces, I have used anonymous but not getting "edit credentials" and have cleared credentials, edited, etc. It won't work now with anonymous (or anything else). Do you know what I am doing incorrectly?

Postman works, but I'm getting a (400) Bad Request in PQ. I am thinking special characters in password might be messing with parsing the parameter values? I bet the % sign could cause a problem.

Hey Parker, this is amazing content. Great to find this on KZfaq. I have a couple of questions - 1. Is there a way we don't store our username/password on the .pbix file? Instead use some Azure app authentication to generate the refreshable token? 2. If have to use username/password, how do we bypass MFA of an organization?

My question is what about the situation when I am pulling 1 million records from REST API and have 5 minutes expiration time of token? As I noticed Power BI can not refresh token making the another API call to REST API

Have you tried granting "Application permissions" instead of "Delegated permissions" and then create a Client Secret ?

Anybody have issues with the post request via postman? Doesn't seem to log in

I have a question. Is it possible to take the workspace id and the dataset id and insert it into the url for getting the "Datasets - Get Refresh History in Group"? Datasets - Get Refresh History In Group (Power BI Power BI REST APIs) | Microsoft Docs Then we could visualize the failed dataset refreshs in report. Thank you in advance.

How about when you are in a large organization and do not have, or even know who has, Admin access to Azure?

Help, Please. I am following and understand what we are doing, but I get an empty table back in PBI Desktop. It tries to create a "Change Type" after configuration in the advanced editor. It converts it to a HTML.Table. I noticed in the video that you have the Web.Contents passed in to the Json.Document as an argument. I've tried to mess with it to get it to return Json but its just not working. I guess the real problem is that it is returning an empty table. ANY help would be greatly appreciated. Thank You in advance :)

I managed to grab the refreshable token, however when trying to use the token I just get the error "We couldn't authenticate with the credentials provided.Please try again." Does anyone have any ideas on why I might be getting this error? It seems to be occuring at the myorg/admin/groups level. It works perfectly when I use the manual refresh token we got in part 1.

looks like it now requires a parameter called client_secret which should be informed with the app secret -- at least that's what I experienced on my side

When I try to run the query in Power BI, I get prompted: "Please specify how to connect". At the same time Postman sends a query as expected. What am I be doing wrong?

If someone could help me Id be thankfull. I did every single step and then I received a Bad Request (400). Aparently I'm using a GET request instead a POST as says so Microsoft. Any ideas what it could be ?

As per what I’ve faced, headers only working in power bi desktop but when you publish it to power bi service it’s not you will get error

Love your videos Parker, but unfortunately the user name and password part are an issue for us that has always been the struggle. When you grant admin consent to the tenant that also has a few security concerns. Think, if a user somehow got this pbix file in an org they could possibly get access to an account that could lead to a data risk if that account has permissions to workspaces, reports, datasets and or dataflows that they normally shouldn't see. For that reason since our user base doesn't have access to Power Automate I've created a custom REST API connector there and did the same thing (could've used powershell as well, but we want to use the power platform atm), then preserve the data in a secure SQL database which is called by a dataflow. Long explanation to say before you do this: Warning.

Hi Parker, Thanks for the detailed video. I am able to get the access token in Postman but while Adding the Get Access Token Function in Workspace code . I am unable to access the API using Anonymous option. I am getting the following error: "We couldn't authenticate with the credentials provided. Please try again".

That app registration page is now hella broken, any help on that?

When Part3 is coming out?

Refresh PowerBI Token is now not working anymore. "This application is using Azure AD Graph API, which is on a deprecation path. Starting June 30th, 2020 we will no longer add any new features to Azure AD Graph API. We strongly recommend that you upgrade your application to use Microsoft Graph API instead of Azure AD Graph API to access Azure Active Directory resources.". Any solution for this?

норм. респект 📌 с двухфакторной аутентификацией только не работает. Нужен Oauth 2.0

I was hoping you'd have a clever and more secure way to store the access token. I can't see that having your password as plain text in the HTTP request as being very secure. Anyone with access to download your PowerBi file can just load up power query and see your password. Good video though!

FYI this won't work anymore as Microsoft put a limit on getgroupsasadmin. "Maximum of 50 requests per hour, per tenant. This call will also time out after 30 seconds to prevent adverse effect on the Powe BI service.