Forum Discussion
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
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
Hello Anonymous
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
