Gathering data from API in Power BI

[Analitika]

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?

[Digger]

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

•