Retrieving data from REST API

[brunborg]

So I need to retrieve data from a REST API. 

Using Json.Document(Web.Contents(<url>)), I am able to retrieve a list of Voyage objects, each looking like this:

[
    {
        "remarks": [],
        "key": 28931,
        "self": "https://<url>/ws/rest/Voyage/28931"
    },
...
]

So far, so good. I am able to publish and schedule refresh. However, I need to get additional data. There are two ways to do this, one (the proper way) is to specify the "fields" header to return additional fields, like this (with fields:{"voyageResult":"*"}😞

[
    {
        "voyageResult": 162967.62,
        "key": 28931,
        "self": "http://<url>/ws/rest/Voyage/28931"
    },
...
]

However, in PowerBI I get the response "The 'fields' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, Content-Type, If-Modified-Since, Prefer, Referer" 

using Basic authentication.

The other approach, is to utilize the "self" key in the response above to get the full record. Performance is ... slow, but it is possible by using Table.AddColumn(#"Expanded Column1", "Voyage Details", each #"Get Details"([Column1.self])) - however when publishing the report, PowerBI tells me that "You can't schedule refresh for this dataset because the following data sources currently don't support refresh: Query contains unsupported function. Function name: Web.Contents"

This is the function Get Details:

let fGetDetails=(self as text) =>

let
    Source = Json.Document(Web.Contents(#"self")),
    #"Converted to Table" = Record.ToTable(Source)
in
    #"Converted to Table"
in
    fGetDetails

[ImkeF · Accepted solution]

Hi @brunborg 

I believe the error-message is misleading. Web.Contents should work in the service.

But you have to hardcode at least parts of the main URL and "overwrite" that with the dynamic content from the function parameter in the headers like described here: https://www.thebiccountant.com/2018/03/22/web-scraping-2-scrape-multiple-pages-power-bi-power-query/

[brunborg]

Thank you, @ImkeF , but no luck.

If I limit myself to just the first retrieval, I can schedule refresh - but with very little data. That line of code is 

Source = Json.Document(Web.Contents("<url>", [RelativePath="ws/rest/Voyage/", Query=[pageNumber="1",limit="20"]]))

So far, so good. But, these records basically only contain a key and a link to a "self"-URL, so I strip off the generic part of the URL, until it reads e.g. "ws/rest/Voyage/1234":

#"Replaced Value" = Table.ReplaceValue(#"Expanded Column1","<url>","",Replacer.ReplaceText,{"Column1.self"}),
#"Invoked Custom Function" = Table.AddColumn(#"Replaced Value", "Voyage Details", each #"Get Details"([Column1.self]))

with the function "Get Details" now as follows:

let fGetDetails=(self as text) =>

let
    Source = Json.Document(Web.Contents("<url>"),[RelativePath=#"self"]),
// Source = Json.Document(Web.Contents(#"self", [Query=[pageNumber="1",limit="1"]])),
    #"Converted to Table" = Record.ToTable(Source)
in
    #"Converted to Table"
in
    fGetDetails

Having tried with both RelativePath and/or Query, the result is always the same: 

Expression.Error: We cannot convert a value of type Record to type Number.
Details:
Value=
RelativePath=ws/rest/Voyage/1234
Type=[Type]

Also tried to not strip the URL, but pass it to the function unaltered (this is commented out in the function above). With this, I am able to update on Desktop and publish, but cannot schedule the refresh with the error message - and we've gone full circle.

---

But this is in any case not a very good approach, since it will generate a lot of calls to the API.  The solution I would prefer, is the following (due to the "Fields" header which will give me the info I need):

Source = Json.Document(Web.Contents("<url>", [RelativePath="ws/rest/Voyage/", Query=[pageNumber="1",limit="20"], 
      Headers=[Authentication="user: pwd", Fields="{""voyageResult"":""*""}"]]))

Hoever, this gives error message "We couldn't autenticate with the credentials provided." Is there an easy fix for this that I do not see?

[ImkeF]

Hi @brunborg ,

there is a syntactic error in your function. Please shift the closing bracket like so:

let fGetDetails=(self as text) =>

let
    Source = Json.Document(Web.Contents("<url>",[RelativePath=#"self"])),
    #"Converted to Table" = Record.ToTable(Source)
in
    #"Converted to Table"
in
    fGetDetails

[brunborg]

Duh... I have stared at that function for so long I didn't see it. Thanks!