Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Analitika
Post Prodigy
Post Prodigy

Gathering data from API in Power BI

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?

1 REPLY 1
Digger
Post Patron
Post Patron


@Analitika

To retrieve data from a REST API in Power BI that requires authentication using AWS Signature, you can follow these steps:

  1. Open Power BI Desktop and go to the "Home" tab.

  2. Click on "Get Data" and select "Web" from the list of data sources.

  3. In the "Web" dialog box, enter the URL of the REST API endpoint you want to retrieve data from.

  4. Click "OK" to proceed.

  5. 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:

    1. In the Power Query Editor, click on the "Home" tab and select "Advanced Editor" from the "Transform" group.

    2. 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

 

  1. 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.

  2. 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.

  3. Add any additional required headers to the headers variable if necessary.

  4. Click "Done" to close the Advanced Editor.

  5. 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.

  •  

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.