PowerBI Power Query Authentication - R method

[wjb3law]

I'm using R to write a PowerBI query to make a simple API request but am receiving "Access is forbidden" with the my credentials. Has anyone tried using this method?

[mnewatloc]

We haven't used R as an API client, but it should work the same way as API clients in other languages. We have tested with clients written in Python and Java. Would you be willing to share screenshots of the issues you're having?

[wjb3law]

Thanks! Here is the query for powerBI and a screenshot of the access error: let /** BEGINNING OF USER CONFIGURABLE OPTIONS

Review and update the configuration items below with the appropriate values
for the target Congress.gov (CGOV) API environment and client credentials
*/

/**************************************************************************
cgov_api_endpoint_url is the fully-qualified URL for the CGOV API endpoint 
you want to retrieve data from

    Example value: https://api.congress.gov/v3/bill

*/    

cgov_api_endpoint_url = "https://api.congress.gov/v3/bill",

/**************************************************************************
personal_access_token is the token generated through the Personal Access 
Tokens page in CGOV

    Example value: 24B1497BFD20399ADAB1B8F6D06B204FF
    (note this example is a made-up token and is not valid in any specific CGOV         
    instance)
*/

cgov_api_key = "[insert api key]",

/**************************************************************************
request_timeout_in_minutes is used to determine how long the web request
to the CGOV API will wait for data to return before timing out. If requests
using this script time out waiting for a response from the API, consider 
increasing this value and trying again.    
*/

request_timeout_in_minutes = 10,

/**************************************************************************
END OF USER CONFIGURABLE OPTIONS - DO NOT EDIT ANY CONTENT BELOW THIS LINE

The remaining portion of this script will use the values provided above
to retrieve data from the CGOV API endpoint URL.
*/

data = Table.FromRecords(
    Json.Document(
        Web.Contents(
            cgov_api_endpoint_url,
                [
                    Headers = 
                    [
                        #"Authorization"="Bearer "&cgov_api_key,
                        #"Content-Type"="application/json"
                    ],
                    Timeout = #duration(0,0,request_timeout_in_minutes,0)
                ]
            )
        )
    )

in data

[apreiter18]

Hello @wjb3law - We wanted to check and see if this issue was still occurring for you. Please let us know and, if not, can you close this issue? Thank you!

[wjb3law]

Greetings, the access forbidden message still prevents the query from executing. API Key is valid and the url is also valid. Unsure of why it is failing

[jonquandt]

@wjb3law - I think you need to modify the way you are passing your key. Per the api.data.gov documentation, if passing via a header, you should pass the X-Api-Key:

https://api.data.gov/docs/developer-manual/#api-key-usage

Your M code is passing the key as a bearer token.

I think if you change this line: #"Authorization"="Bearer "&cgov_api_key,

to read

#"X-Api-Key"=cgov_api_key, (accounting for any formatting/closing " issues)

it should work.

Doing a simple Get Data from web in Power Query using https://api.congress.gov/v3/bill and the basic DEMO_KEY returns information, so I think that your header specification is incorrect, causing the error message back.

[wjb3law]

Thank you! I reset my header as suggested and this resolved the access forbidden message. I am now receiving "We cannot convert a value of type Record to type List. Details: Value=[Record] Type=[Type]"

[jonquandt]

@wjb3law - since your API access issue is resolved, I think your next step would be to do some additional digging into Power Query documentation or communities tailored to that tool.

From my experience with PowerQuery, you may need to back up a few steps and start with the most basic request. It seems like you're trying to combine steps converting the json response directly into a table instead of getting the json back and then implementing a step to convert to a table (which is admittedly, usually an automatic transform step that happens when you create an initial query like this).

[wjb3law]

Thanks! I appreciate the help. I was able to get it to work and have data pulled successfully. I copied an automatic transform function from json to list and then added those parameters to the header arguments for a web query.

[apreiter18]

If it is alright with you, @wjb3law, I am going to close this issue. Please let me know if you have any questions - thanks!