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.
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?
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
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).
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.