Scheduling a multiple REST API call with Power BI Service

[jan_k123]

In Desktop I got a parameterized function working feeding off a REST API service. Then I wanted it to be scheduled on Power BI Service. But got this error:

Query contains unsupported function. Function name: Web.Contents

I have used 2 parameters:  (start as text, count as text) => 

let qQQQ = (start as text, count as text) =>
let
    Bron = Xml.Tables(Web.Contents("https://api.QQQ.com?qualifier=effectiveDate%3E%3D(NSCalendarDate)'2019-01-01%2000%3A00%3A00'%20AND%20attributeEntityName%20%3D%20'CRMatch'%20AND%20attributeName%3D'statusInfo'&start="&start&"&count="&count&"&ordering=(%7Bkey%3DeffectiveDate%3Bsel%3DDescending%7D)&show=effectiveDate&show=effectiveEndDate&show=id1&show=id2&show=toMatch.owner.userID&show=attributeValue.displayName&show=attributeValue.statusInfoID"))
in
    Bron
in qQQQ

Then I applied this against a list:  = List.Generate(()=>0, each _ < 67000, each _ + 1000) 

In a new query i invoked a custom function with Column1 = the List function and Column2 = 1000. After that I add the web-query which has the start and count params. 

The result should be it gets 1000 records at a time, otherwise it fails/time-out. In PBI Desktop it works, but on Service I can't schedule it. 

Can you help me out with the refresh on PBI Service? (I am using basic auth with user and pass)

Thanks! 

[blopez11]

The power bi service doesn't like dynamic URLs as the first paramenter to Web.Contents.  You need to break up your URL using the Web.Contents RelativePath and Query options.  The first parameter to Web.Contents is usually the root URL, then you use RelativePath and Query options to extend

See the below for details: https://docs.microsoft.com/en-us/powerquery-m/web-contents

[jan_k123]

So I need to get in url and possibly the qualifiers and show's in as well?

(url as text, qualifiers as text, start as text, count as text) =>

How does that look like after the Web.Contents(" ?

Not entirely sure I understand the RelativePath and Query options. Can you explain or give an example? Thanks. 

[blopez11 · Accepted solution]

Here is an excellent reference that helped me

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...