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 ...
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"
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=counties
Here 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"