Web Contents does not return json data from web API

[Kartnepe]

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

[lbendlin]

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?

[lbendlin · Accepted solution]

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"