Get data from web error: A web API key can only be specified when a web API key name is provided

[abedkhooli]

I have PBI desktop Version: 2.40.4554.463 64-bit (October, 2016) and I am trying to get web data from opensensors using an API with a key. I tried several combinations in the header (one is shown below) but I always get the error: "A web API key can only be specified when a web API key name is provided".

Here's a working curl request:

curl -X GET --header "Accept: application/json" --header "Authorization: api-key 428************2c4" "https://api.opensensors.io/v1/messages/topic/%2Forgs%2Fwd%2Faqe%2Fparticulate%2Fegg008******2?start-..."

What should I use for headers? Is there an issue with this PBI version? 

PS: I read the thread at https://community.powerbi.com/t5/Service/web-contents-with-specified-headers-works-in-PBI-desktop-bu...

[abedkhooli]

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.

 

[lukaszp]

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

[Anonymous]

Hi Lukasz

I am trying to do something similar.  I am trying to query the MS Cognitive Services Text Analytics API via the function below.   As that API is asynchronous I need to extract the Operation-Location header value in order to retrieve the results.   However, the Web.Contents function seems to only return Response Status.

For testing, I have modified the function to return the headers for the response metadata as per your example code but all I get is Content-Type field with a null value.   Am I right in summising that it is not possible to extract response header data (except for status) and therefore this sort of processing is only possible if link/location values are retuned as content rather than in headers?

(Source as table) as any =>
let
JsonRecords = Text.FromBinary(Json.FromValue(Source)),
JsonRequest = "{ ""stopWords"": [], ""topicsToExclude"": [],""documents"": " & JsonRecords & "}",

JsonContent = Text.ToBinary(JsonRequest, TextEncoding.Ascii),
Response =
Web.Contents("https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/topics",
[
Headers = [#"Ocp-Apim-Subscription-Key"= "xxxxxxxxxxxxxxxxxxxxxxxxxx",
#"Content-Type"="application/json"],
Content=JsonContent,
ManualStatusHandling={202}

]),
ResponseMeta = Value.Metadata(Response),
ResponseStatus = ResponseMeta[Response.Status],
Output = if ResponseStatus = 200 then Json.Document(Response,1252) else ResponseMeta[Headers]
in
Output

[lukaszp]

This question is a litle beyond my M mastery 🙂 @mllopis and @TPalmer could you help?

[mllopis]

Hi all,

In order to be able to supply an “Api Key” via the credential UI, the user’s M has to look like this:

Web.Contents(URL, [ApiKeyName="mykey"])

If the user enters a key of SECRET, this will produce a final URL of URL?mykey=SECRET

NOTE: this is not supported in cloud refresh.

With respect to other messages on the thread,

1. There’s no way to set arbitrary HTTP request headers through the credential system.
2. There’s no way to get arbitrary HTTP response headers

Hope this helps.

[BrentonC]

"NOTE: this is not supported in cloud refresh."

Can you point me towards the documentation that says this? I have been trying to get this working for a while, although have not come across this in the documentation. It could have saved me some time. 

https://learn.microsoft.com/en-gb/powerquery-m/web-contents

[Sokon]

Obviously, there was no OData feed-Connector available in earlier versions of PBID, right?

Because now, there is one - which doesn't mean much, because the error stays the same:

I would expect the build-in editor is able to compose correct API-Queries. If so, what do I do wrong? (Not many options here, right)

[abedkhooli]

Thanks Lukas. 
I must be doing something wrong with my zero level knowledge in M. Could not return any data. 

Rather than learn M, I am trying to use a relatime API that is being developed. I get data returned but it takes ages to process each step after "source" - so far over 1 hr working on a "starts with" filter and the whole data is under 3500 rows.

[lukaszp]

Not sure what your API is doing. If it doesn't close the HTTP response, I bet the query tab is just sitting there waiting for the rest of the data.

[abedkhooli]

This seems very possible. I tried sending Connection: Close in the header but did not make a difference. My guess is that the API is ignoring it. I also used command timeout = 1.5 min (device reads every 60 sec). Waiting for feedback from the API developer.