Power Query/Power BI REST API Couldn't Authenticate with Credentials

[bigmac025]

Hello,

I am trying to automate Power BI REST API to get tenant information such as Users, Workspaces, Reports, etc.

I am having a problem with my query that calls a function to get the Access Token.  I keep getting We couldn't authenticate with the credentials provided.

My function I created (Get Access Token()) works everytime and generates the access token.  I have tested in postman and it all works, but when I call the function for the Access Token I get the error.

This function works everytime when I manually invoke it.

Any help would be appreciated.

Thanks in advanced.

Tim

[v-tsaipranay]

Hi  @bigmac025 

We haven't heard back from you regarding our last response. We hope your issue has been resolved.

If the my answer resolved your query, please mark it as "Accept Answer" and select "Yes" if it was helpful.

If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum.

[v-tsaipranay]

Hi @bigmac025 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-tsaipranay · Accepted solution]

Hi @bigmac025 

Thank you for your query regarding the automation of data retrieval from the Power BI REST API.

I reviewed the Power Query M code you provided. It aims to authenticate and retrieve tenant information, but I have identified a few necessary adjustments:

• API Endpoints: Use the base URL https://api.powerbi.com and ensure you target appropriate endpoints such as /v1.0/myorg/groups for workspaces.
• Request Formatting: Ensure the JSON body uses double quotes for keys and values. The headers must correctly spell "Authorization."

Below is the code:

let 

    url = "https://api.powerbi.com", // Base URL for the Power BI REST API 

    body = "{ ""client_id"": ""1232cd11-7862-1111-83c2-28f5a0b6ce0a"", ""client_secret"": ""QopQopQop133111"", ""grant_type"": ""client_credentials"" }", 

    token_response = Web.Contents( 

        url, 

        [ 

            RelativePath = "/auth/o2/token", // Change to your actual token endpoint 

            Headers = [#"Content-Type" = "application/x-www-form-urlencoded"], 

            Content = Text.ToBinary(body) 

        ] 

    ), 

    access_token = Json.Document(token_response)[access_token], 

    accounts_response = Web.Contents( 

        url, 

        [ 

            RelativePath = "/v1.0/myorg/groups", // Correct endpoint for workspaces 

            Headers = [Accept = "application/json", Authorization = "Bearer " & access_token] 

        ] 

    ), 

    Result = Json.Document(accounts_response) 

in 

    Result

• I recommend testing the API calls in Postman to verify your credentials and responses. Additionally, consider implementing error handling to enhance reliability. This approach may resolve your issue.

As you have requested alternative options, I would like to present the following steps for your consideration:

• Instead of using Power Query alone, consider using Azure Data Factory or Power Automate to automate data retrieval from the Power BI REST API.
• Use these API endpoints:
• Users: /v1.0/myorg/users, Workspaces: /v1.0/myorg/groups, Reports: /v1.0/myorg/reports, Apps: /v1.0/myorg/apps
• Authenticate with a Service Principal or a User Account that has the necessary permissions.
• Store the retrieved data in Azure SQL Database or Azure Blob Storage for easy access.
• In Power BI Desktop, connect to your data source (Azure SQL Database or Blob Storage).
• Create visualizations to display the data.
• Set up a scheduled refresh in Power BI Service to keep your report updated.
• Use the subscription feature to automatically send the report to stakeholders weekly.

This approach should help you effectively gather and report the necessary information. If you need further assistance with any of these steps, please feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou

[bigmac025]

Hello @v-tsaipranay 

I've decided to go the script/Power Automate path and retrieve data from the Power BI REST API and save it to SQL Server.

I'm testing in Postman first to make sure I have everything right but I am running into problems.  I want to be able to call the API without credentials.

Here is my call to get the access token:

I get an access token without any issues.

I then call the group API using the access token and I get a 403 Forbidden.

I have registered an application for Power BI in Azure and granted permissions outlined on this web page https://www.sqlshack.com/how-to-access-power-bi-rest-apis-programmatically/

I have also performed these steps:

• Make sure you have added the Azure AD Group in the Power BI portal by enabling Allow service principals to use read-only admin APIs option.
• Make sure that API permissions are granted Admin consent by the Global Admin.
• Ensure that the Azure AD Application and Dataset are in the same tenant.
• Generate the access token via Authorization Code Flow as Dataset.Read.All is a delegated API permission.

I still get the 403 Forbidden. When I copy the access token from the Microsoft Learn REST API Try it and paste it into Postmand it works.  It must have something to do with security but I'm stumped.

Any assistance would be appreciated.

Thanks,

Tim

[bigmac025]

Thanks @Greg_Deckler.  That would actually make sense if it is not supported because I have tried everything and can't make it work.

Can you point me in the right direction to what would be the best way to create a Power BI report that shows all Power BI users, workspaces, apps, reports, etc?

It would be really handy to have all this information in a PBI report that gets sent out weekly.

Thanks,

Tim

[Greg_Deckler]

@bigmac025 I have never gotten the Power BI REST API to work in Power Query, I'm not certain it supports it tbh.