topic Web Contents does not return json data from web API in Developer

Web Contents does not return json data from web API

Anonymous — Fri, 08 Apr 2022 07:58:53 GMT

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

Re: Web Contents does not return json data from web API

lbendlin — Sat, 09 Apr 2022 20:59:49 GMT

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?

Re: Web Contents does not return json data from web API

lbendlin — Sat, 09 Apr 2022 21:27:57 GMT

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"