Forum Discussion
Web Contents does not return json data from web API
The Road Administration in Norway provides trafficdata in an web api using GraphQL (https://www.vegvesen.no/trafikkdata/api/). I have unsuccessfully tried to set up a connection and load data into a table in Power BI. It seems like web.contents returns html for the website rather than json data. I have run the query in Postman and there it returns correct json data.
I appreciate some tips that can lead me the right way:-)
This is my code:
let
uri = "https://www.vegvesen.no/trafikkdata/api/",
headers = [#"Content-Type" = "application/json"],
query = "
query counties {
areas {
counties {
number
name
}
}
}
",
source = uri & "?query=" & Uri.EscapeDataString(query),
resp = Web.Contents(source, [Headers=headers]),
json = Json.Document(resp)
in
json
Ah, never mind. Having a look at how they actually call the API reveals that this is a POST request, which means you need to stuff the query into the payload instead.
let query = "{areas{counties{number name}}}", uri = "https://www.vegvesen.no/trafikkdata/api/", Source = Json.Document(Web.Contents(uri, [Headers=[#"Content-Type"="application/json"], Content=Json.FromValue([query = query])])), #"Converted to Table" = Table.FromList(Source[data][areas][counties], Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"number", "name"}, {"number", "name"}) in #"Expanded Column1"
2 Replies
The problem seems to be that the website is trying to be cute, and is providing a placeholder "Loading..." page first
You would have to ignore that and fetch the next page.
Your code is also missing an important query parameter.
https://www.vegvesen.no/trafikkdata/api/?query=query%20counties%7Bareas%7Bcounties%7Bnumber%20name%7D%7D%7D&operationName=countiesHere is the "proper" Power Query code that you shoud be using.
Parameters uri and query uri = "https://www.vegvesen.no/trafikkdata/api/" query = "query counties{areas{counties{number name}}}" Request Web.Contents(uri, [Headers=[Accept="application/json"], Query=[query=query,operationName="counties"]])ImkeF Do you remember how to handle URI redirects in such a scenario?
Ah, never mind. Having a look at how they actually call the API reveals that this is a POST request, which means you need to stuff the query into the payload instead.
let query = "{areas{counties{number name}}}", uri = "https://www.vegvesen.no/trafikkdata/api/", Source = Json.Document(Web.Contents(uri, [Headers=[#"Content-Type"="application/json"], Content=Json.FromValue([query = query])])), #"Converted to Table" = Table.FromList(Source[data][areas][counties], Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"number", "name"}, {"number", "name"}) in #"Expanded Column1"