Automate skip and take for API call via power query

[Anonymous]

Hi, 

I have created a blank query, see below, to get data from a software API directly into power bi. Recently the software company put a restriction on the amount of data which can be requested in one call. I have 3 tables which are over 250.000 records and i cannot call upon them now because I get a service error. The software company tells me I will need to work with their "skip and take" functionality which lets you get the data in batches. I can manually copy the query below 20 times and change the skip take in each query and append them in power bi. See example. 

query 1. skip 0 take 10000

Query 2. Skip 10000 take 10000

Query 3 Skip 20000 take 10000

But this is a lot of work and i wondered if there is a better way of doing this where i can choose the take amount and the skip amount dynamicly without copying the query 20x. 

this is the blank query for the request:

let
Source = Xml.Tables(Web.Contents(""& URL &""
,[Content=Text.ToBinary("<soapenv:Envelope xmlns:soapenv="&Character.FromNumber(34)&"http://schemas.xmlsoap.org/soap/envelope/"&Character.FromNumber(34)&" xmlns:urn="&Character.FromNumber(34)&"urn:Afas.Profit.Services"&Character.FromNumber(34)&">
<soapenv:Header/>
<soapenv:Body>
<urn:GetData>
<urn:token>
<![CDATA[
"& Token &"
]]>
</urn:token>
<urn:connectorId>"& Connector &"</urn:connectorId>
<urn:skip>0</urn:skip>
<urn:take>999999999</urn:take>
</urn:GetData>
</soapenv:Body>
</soapenv:Envelope>"),Headers=[#"Content-Type"="text/xml;charset=UTF-8"]]))
in
Source

I hope you can help me

[mahoneypat]

You can start your query with = List.Numbers(0, 25, 10000), convert that to a Table, and then concatenate that into your web call as the Skip value.  You will then have a table of 25 tables, and you can expand it to combine all the data.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[artemus]

Please don't use List.Numbers for this, as you will call the API more than necessary. You want to stop once you get to the end of the item list.

Instead use List.Generate or an actual recursive function. Such as:

let 
   nextPage = (start) => 
      let 
          values = Xml.Tables(Web.Contents("https://example.com/?start=" * Text.From(start) & "&end=" & Text.From(start + 25)),

       in
           if Table.RowCount(values) < 25 then
              values
            else
               values & @nextPage(start + 25)

[mahoneypat]

List.Numbers is a good approach for this.  I usually first make a call to get the total count, divide that by the rows per call, and use that number as the # of items in the List.Numbers.  That way, you make just the right # of calls.

Regards,

Pat

[lbendlin]

You can write a recursive function that increments the skip count after each iteration.  Just make sure you know when to stop  (probably when the number of returned rows is less than the number of requested ("take") rows.