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
Solved! Go to 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"
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"
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
5 | |
4 | |
2 | |
1 | |
1 |
User | Count |
---|---|
17 | |
10 | |
2 | |
2 | |
2 |