Forum Discussion

jwillis07's avatar
jwillis07
Advocate I
5 years ago
Solved

How to use the $Skip ODATA expression in a loop?

Good afternoon all,   I'm trying to call all of the results within an API that has: 6640 total records 100 records per page 67 pages of results (total records / records per page) This is an ev...
Need Help
Tutorial Requests
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    A simpler approach is shown in this article/video - Power BI - Tales From The Front - REST APIs - YouTube

    You can adapt that approach.  Basically, you create a list of number that increment by 100 with List.Numbers, convert that to a table, make your number column a text column and then concatenate the number into your URL on each row.  You then expand the column of "Table"s to get your result.

     

    Pat

     

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

Your add column step is still missing the & before [Column1].  Also make sure [Column1] is type text (or convert it to text in this expression).

 

AddColumn   = Table.AddColumn(ColumnType, "Custom", each Json.Document(Web.Contents(BaseURL, [Headers = [Authorization = "Bearer " & Token], RelativePath =  Path & "?" & "$Skip=" & [Column1]])))

  

Pat

 

jwillis07's avatar
jwillis07
Advocate I
5 years ago

Sorry Pat, my bad. I did have the & between "?$Skip=" and [Column1] - Must have copied some old code accross.

 

I have found the answer though in that it's case sensitive and should have been "?$skip=" - A capital S caused me that much grief.

 

But I love your solution for paginating the results so definitely using that one going forward, thank you very much!