Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Automate skip and take for API call via power query

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 ...
Need Help
artemus's avatar
artemus
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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