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

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