Web.Contents - Desktop to Service

[Anonymous]

Hello Everyone,

I have been working on this for awhile, but I am just lacking a proper understanding of how Web.Contents is used between the Desktop and Service.  Here is my delima:

I have Power M query to iterate page by page from an API (thank you @ImkeF for you wonderful assistance), which works just find from the Desktop.

let
WebAPI = "https://api.getdata.com/surveys/clientid/responses",
BearerToken= "Bearer XXXXXXXXXXXXXXXXX",
PageLimit = 20,
Pagination = List.Buffer(List.Generate( () => [WebCall=[], Page = 1, Counter = 0], 
   		each (try List.IsEmpty([WebCall][client_data])=false otherwise false or [Counter]=0) and [Page]<=PageLimit,
   		each [ WebCall = Json.Document(Web.Contents(WebAPI&"?per_page=100&page="&Text.From([Page]),[Headers=[Authorization=BearerToken]])), 
     			Page = [Page]+1,
     			Counter = [Counter]+1
]
))
in
    Pagination

 The troube begins when I publish to the Service and schedule the data for refresh: "Query contains unsupported function. Function name: Web.Contents".  I have a loose understanding that the base URL isn't being recognized as a data source in the Service, but I do not know how to format the code in a way that trips up the Desktop to establish the base URL as a authenticated datasource that can be refreshed in the Service.  What am I missing?

Thanks for the help in advance,

Woody

[Stachu]

based on this post

https://community.powerbi.com/t5/Power-Query/Exchange-rates-Data-not-refresh/m-p/909612

it seems that you need a gateway installed

[Anonymous]

@Stachu - Based on what the page you gave me, a gateway for web is "Required for .html, .xls, and Access Databases", but in my case, this is cloud based API, not something local.  I have another Power M query which only requires one pass at the API and it can be scheduled in the Service without issue, so I am not sure it is the gateway is the answer.   

[ImkeF · Accepted solution]

Hi @Anonymous ,

problem is the dynamic URL.

You have to use the relative path for it instead: http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/ 

[Anonymous]

@ImkeF - I just had to take a fresh look, once we the Static Url was set, it worked like a charm.   Here is the code snippet:

Pagination = List.Buffer(List.Generate( () => [WebCall=[], Page = 1, Counter = 0], 
   		each (try List.IsEmpty([WebCall][active_models])=false otherwise false or [Counter]=0) and [Page]<=100, 
        each [ WebCall = Json.Document(Web.Contents("https://api.getdata.com",
           [RelativePath="surveys/clientid/responses/?per_page=100&page=" & Text.From([Page]),
           Headers=[Authorization="Bearer XXXXXXXXX"]])), 
     			Page = [Page]+1,
     			Counter = [Counter]+1

Set the datasource "https://api.getdata.com" to Anonymous and then skip connection when it is the service. 

[Anonymous]

@ImkeF  - We started trying to give a static URL within the Web.Contents and add the Relative path, but we aren't getting an results.  Here is what we changed: 

let

BearerToken= "Bearer XXXXXXXXXXXXXXXXX",
PageLimit = 20,
Pagination = List.Buffer(List.Generate( () => [WebCall=[], Page = 1, Counter = 0], 
           each (try List.IsEmpty([WebCall][client_data])=false otherwise false or [Counter]=0) and [Page]<=PageLimit, 
           each [ WebCall = Json.Document(Web.Contents("https://api.getdata.com/surveys/clientid",
           [RelativePath="/responses?page=" & Text.From([Page]),
           Query=[Headers=[Authorization=BearerToken]]])), 
                 Page = [Page]+1,
                 Counter = [Counter]+1
]
))
in
    Pagination

Is there something wrong with syntax of the Relative Path parameter? 

w.