cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

REST API doesn't seem to receive access token

With the following code I'm trying to connect a Power BI Desktop workspace to a Web API (note that I anonymized all the identification codes, I'm not working with XXXXXXX as values. These hide the string of cyphers). 

let
url = "https://api.XXXXYYYXXX.com/oauth/v2/token?",
urlClient = "https://api.XXXXYYYXXX.com/v2/export/instances/46602/detailed/",

GetJson = Web.Contents(url,
[
    Headers = [#"Accept"="application/json",
    #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
    Content = Text.ToBinary("client_id=XXXXXXXXXXXXXX&client_secret=XXXXXXXXXXXX&grant_type=XXXXXXXXX&api_key=XXXXXXXXXX")
]
),

FormatAsJson = Json.Document(GetJson),
AccessToken = FormatAsJson[access_token],
TokenType = FormatAsJson[token_type],
AccessTokenHeader = TokenType &" "& AccessToken,
TokenUrl = [Headers=[Authorization=AccessTokenHeader]],

ExternalData = Csv.Document(Web.Contents(urlClient, TokenUrl))

in
ExternalData

Every piece of code does exactly what it is expected to do.

The GetJson gets the JSON-formatted data
The data is parsed to a table. The right information gets extracted from the table. The Access Token is extracted and put into string. 


Eventually, the variable ExternalData should have the url to the API + bearer-TokenType + Access Token code. It should have all the information it needs to get through the firewall and extract the data. 

Then, when In starts, it goes awry. I get the following error: 

DataSource.Error: Web.Contents failed to get contents from 'https://api.XXXYYYXXX.com/v2/export/instances/46602/detailed/' (404): Not Found
Details:
    DataSourceKind=Web
    DataSourcePath=https://api.XXXYYYXXX.com/v2/export/instances/46602/detailed
    Url=https://api.XXXYYYXXX.com/v2/export/instances/46602/detailed/

I have tested the API with Postman, and I get all the data I expect - in CSV-format - so the API works. Postman gets through the firewall and can extract the data. Why does Power BI stumble upon a 404? I think the access token doesn't get send out again, keeping the firewall up... but I don't know for sure. 

What am I not getting? Where is the code not working properly?

2 REPLIES 2
New Member

Hi Thijs,

Try making the call like this:

GetJsonQuery = Web.Contents("https://xxxxxxxxxxx:xxxxx",
[
Headers = [#"Authorization"=AccessTokenHeader],
RelativePath="v2/export/instances/46602/detailed"

//if you have query params in your case not 
//Query =
//[
//api_key="xxxxxxxxxxxx",
//clientId=ID
//]
]
),

 

The reason your calls break is that the URL must be broken up.

Important: The first part(base url) "https://xxxxxxxxxxx:xxxxx" must resolve without creds! must return 200

 

I hope this helps you i have been through the same.

Good luck

Advocate II
Advocate II

Sorry for only answering now, but Fiddler could help you detect the differences between the request made with Postman and with Power Query. 

Something about your url is being modified and resulting in a non existant one (404).

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

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

Top Solution Authors
Top Kudoed Authors