Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi,
I would like to ask how I could retrieve data from REST API in Power BI. I have an issue. Whenever I am trying to make request for gathering data, I am getting an error - access is forbidden. It is due to authentification. It uses AWS Signature. I tried to pass headers in get data but still getting access is forbidden. How I could resolve this issue?
To retrieve data from a REST API in Power BI that requires authentication using AWS Signature, you can follow these steps:
Open Power BI Desktop and go to the "Home" tab.
Click on "Get Data" and select "Web" from the list of data sources.
In the "Web" dialog box, enter the URL of the REST API endpoint you want to retrieve data from.
Click "OK" to proceed.
In the "Navigator" dialog, select the desired tables or data elements from the API response and click "Transform Data" to load the data into the Power Query Editor.
To authenticate and pass the necessary headers for AWS Signature, you'll need to use Power Query's custom function capabilities. Here's an example of how you can achieve this:
In the Power Query Editor, click on the "Home" tab and select "Advanced Editor" from the "Transform" group.
In the Advanced Editor, replace the existing code with the following template:
let
// Define your AWS credentials and parameters
accessKey = "<Your AWS Access Key>",
secretKey = "<Your AWS Secret Key>",
region = "<Your AWS Region>",
serviceName = "<Your AWS Service Name>",
requestUri = "<The API endpoint URL>",
// Define the function to generate the AWS Signature
generateAWSSignature = (accessKey, secretKey, region, serviceName, requestUri) =>
let
...
// Implement the logic to generate AWS Signature
...
in
signature,
// Generate the AWS Signature for the request
signature = generateAWSSignature(accessKey, secretKey, region, serviceName, requestUri),
// Define the headers required for AWS Signature authentication
headers = [
Authorization = signature,
...
// Add other required headers if needed
...
],
// Make the HTTP request with the headers
source = Web.Contents(requestUri, [Headers = headers])
in
source
Replace the placeholders <Your AWS Access Key>, <Your AWS Secret Key>, <Your AWS Region>, <Your AWS Service Name>, and <The API endpoint URL> with your actual AWS credentials and API details.
Implement the logic within the generateAWSSignature function to generate the AWS Signature based on the AWS Signature Version 4 algorithm. You can refer to the AWS documentation for the exact steps and code implementation.
Add any additional required headers to the headers variable if necessary.
Click "Done" to close the Advanced Editor.
Click "Close & Apply" to load the data into Power BI.
By implementing the AWS Signature generation and passing the necessary headers, you should be able to authenticate and retrieve data from the REST API in Power BI. Make sure to replace the placeholders with the appropriate values and modify the code to fit your specific AWS authentication requirements.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
5 | |
1 | |
1 | |
1 | |
1 |
User | Count |
---|---|
9 | |
3 | |
3 | |
2 | |
2 |