Forum Discussion
Get data from web error: A web API key can only be specified when a web API key name is provided
I managed to get this to work [to a good extent] in the November update. Basically, I passed the key after the 2nd header (api-key <key>) and used anonymous in screen 2. Using the interface, I managed to drill down in the json file, split columns and visualize readings over time. Once I had the query generated, I tried it directly with the Oct update and it worked. Here's the query if someone is interested:
let
Source = Json.Document(Web.Contents("https://api.opensensors.io/v1/messages/topic/%2Forgs%2Fwd%2Faqe%2Fparticulate%2Fegg*******2?start-date=2016-12-01T16:20:44.320Z", [Headers=[Accept="application/json", Authorization="api-key 42***********c4"]])),
messages = Source[messages]
in
Source
However, this is actually half-way only. The API returns up to 1000 rows per query and outputs a value for the next query called 'next" (it basically changes the start-date timestamp to original plus time for 1000 readings - or current time if interval from start-date is not enough for 1000 readings).
The question is: can we take this "next" query and automate it so the final table is appended with new values (generate new queries?) each time we hit Refresh (manual streaming or incremental data - for PBI Desktop, of course)? I have no DAX experience.
Ok, since you're going down the rabbit hole :) Here's some M code you should be able to adapt to do what you need. It's taken from a different project I worked on some time ago so it will show you the way, but you'll need to craft your solution. It relies on using M queries as functions.
TPalmer might be able to help here too.
Table.GenerateByPage = (getNextPage as function) as table =>
let
listOfPages = List.Generate(
() => getNextPage(null),
(lastPage) => lastPage <> null,
(lastPage) => getNextPage(lastPage)
),
tableOfPages = Table.FromList(listOfPages, Splitter.SplitByNothing(), {"Column1"}),
firstRow = tableOfPages{0}?
in
if (firstRow = null) then
Table.FromRows({})
else
Value.ReplaceType(
Table.ExpandTableColumn(tableOfPages, "Column1", Table.ColumnNames(firstRow[Column1])),
Value.Type(firstRow[Column1])
),
GetNextLink = (link) =>
let
links = Text.Split(link, ","),
splitLinks = List.Transform(links, each Text.Split(Text.Trim(_), ";")),
next = List.Select(splitLinks, each Text.Trim(_{1}) = "rel=""next"""),
first = List.First(next),
removedBrackets = Text.Range(first{0}, 1, Text.Length(first{0}) - 2)
in
try removedBrackets otherwise null,
GetContents = (url as text) =>
let
content = Web.Contents(url),
link = GetNextLink(Value.Metadata(content)[Headers][#"Link"]?),
json = Json.Document(content)
in
json meta [Next=link],
GetPagedTable = (url as text) => Table.GenerateByPage((previous) =>
let
next = if previous = null then null else Value.Metadata(previous)[Next],
current = if previous <> null and next = null then null else GetContents(if next = null then url else next),
link = if current = null then null else Value.Metadata(current)[Next],
table = if current = null then null else Table.FromList(current, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
table meta [Next=link])
At the end of the day you'll call
Source = GetPagedTable( <<your url>>),
HTH, -Lukasz