Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Proud to be a Super User!
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
Proud to be a Super User!
Thank you very much for your help Philip. I'll reach out to their support.
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
Proud to be a Super User!
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |