cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Web.Contents - Desktop to Service

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

 

 

1 ACCEPTED SOLUTION

Hi @djwoodrow ,

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/ 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

5 REPLIES 5
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

@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.   

Hi @djwoodrow ,

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/ 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

@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. 

@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.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors