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

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'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