cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.