Connecting to an API that uses OAuth with Power Query

[Anonymous]

Hi All,

I'm trying to ingest data from this API: https://api.nsw.gov.au/Product/Index/22

The site lets you test the endpoints in the browser and I get a valid response using the test credentials.

I'm able to get the access token in Power Query.  But the next request which uses the access token doesn't seem to work.  Power BI Desktop tells me the credentials used are not valid... 

M script below... any help would be much appreciated.

//https://api.nsw.gov.au/Product/Index/22
//Visit the site above and click on 'Sandbox and doc'
//This will present test credentials to try the endpoint

let
    testAPIKey = "1MYSRAx5yvqHUZc6VGtxix6oMA2qgfRT",
    testAPISecret = "BMvWacw15Et8uFGF",

    // Concatenates the test Key & Secret and converts to base64
    authKey = "Basic " & Binary.ToText(Text.ToBinary(testAPIKey & ":" & testAPISecret),0),

    url = "https://api.onegov.nsw.gov.au/oauth/client_credential/accesstoken",
    // Make a POST request to obtain a bearer token
    GetJson = Web.Contents(url,
        [
            Headers = 
                [
                #"Authorization"=authKey,
                #"Content-Type"="application/json"
                ],
            Content = Text.ToBinary("grant_type=client_credentials") 
        ]
    ),

    // Get the bearer token from the response
    AccessToken = Text.From(GetJson),

    GetJsonQuery = 
    Web.Contents("https://api.onegov.nsw.gov.au/FuelPriceCheck/v2/fuel/prices",
        [
        Headers=
            [
            #"Authorization"="Bearer " & AccessToken, 
            #"Content-Type"="application/json; charset=utf-8",
            #"apikey"=testAPIKey,
            #"Accept"="application/json",
            #"transactionid"="1",
            #"requesttimestamp"= DateTime.ToText(DateTime.LocalNow(), "dd/MM/yyyy hh:mm:ss tt")
            ]
        ]
    ),
    FormatAsJsonQuery = Json.Document(GetJsonQuery)
in
    FormatAsJsonQuery

[UFP]

Hi,

Allthough this thread is old. Thanks for the almost working example in this post.

I have replaced

AccessToken = Text.From(GetJson)

with

AccessToken = Json.Document(GetJson)[access_token]

Passing this AccessToken as the Bearer works for me.

Hope this helps others in the same situation,

Udo

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

That NSW Govt website is way too hard to use.  It keeps giving me errors just trying to access it, like this

When I generate an access token on the website and then use it from the same website it tells me the access token is invalid.

Generate an Access Token

Make an API Call

Check the Response

If the Access Token the site itself is generating is being rejected then there's not much hope.  I'm getting the same errors in Postman.

If I was you I'd be asking the website support what the issue is at their end.

Regards

Phil

[Anonymous]

Thank you very much for your help Philip.  I'll reach out to their support.

[PhilipTreacy]

Hi @Anonymous 

I'm getting the same error but I can't see anything wrong with your code.

I even signed up for an acount with the website and created myown key/secret and can get an Access Token OK, but auth to the actual API is failing.

I'll check it in Postman and see what is happening.

regards

Phil

[amitchandak]

@Anonymous , check if this can offer a help

https://www.myonlinetraininghub.com/connecting-to-an-oauth-api-like-paypal-with-power-query

https://docs.microsoft.com/en-us/power-query/handlingauthentication

https://community.powerbi.com/t5/Power-Query/OAuth2-REST-API-as-data-source/m-p/414748