Cursor Paginated data from Web API

Hi  ImkeF,

Any chance you can also help me, very similar issue to this thread but I have to drill down a few steps to get my 'next' url

Function GetURL:

(url as text) as table =>
let
    Source = Json.Document(Web.Contents(url)),
    results = Source[results],
    #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "properties", "createdAt", "updatedAt", "archived"}, {"id", "properties", "createdAt", "updatedAt", "archived"}),
    #"Expanded properties" = Table.ExpandRecordColumn(#"Expanded Column1", "properties", {"amount", "createdate", "dealstage", "hs_lastmodifieddate", "hs_object_id", "printing_kpi_by"}, {"amount", "createdate", "dealstage", "hs_lastmodifieddate", "hs_object_id", "printing_kpi_by"})//& Number.ToText(after))),
in
   #"Expanded properties"

the result if just requesting the first URL & not drilling down (apologies for using a table but having errors every time I try to load screenshots):

The next URL is within the 'paging' records, the following steps are required to get to it:

let
source = Json.Document(Web.Contents("https://api.hubapi.com/crm/v3/objects/deals?limit=100&paginateAssociations=false&archived=false&hapikey=APIKEY&properties=dealstage&after=0")),
    #"Converted to Table" = Record.ToTable(source),
    Value = #"Converted to Table"{1}[Value],
    next = Value[next],
    #"Converted to Table1" = Record.ToTable(next)

in
#"Converted to Table1"

Which returns the following table:

This is the code I am trying to tweak based on this thread, I am just unsure how to reference the next link correctly 

let
myList = List.Generate( ()=>
    [Result = GetURL("https://api.hubapi.com/crm/v3/objects/deals?limit=100&paginateAssociations=false&archived=false&hapikey=APIKEY&properties=dealstage&after=0"), Counter = 0],
    each  [Counter] < 5,
    each [Result =  GetURL(Table.LastN([Result]{[Name = "link"]}[next], 1){0}[#"Attribute:value"]), Counter = [Counter] +1],
    each [Result])
in
    myList

It returns a list of 5, row 1 table pulls through correctly as 'Table' the next 4 show as errors.

"Expression.Error: the key didn't match any rows in the table

Details:

 Key = 
   Name=link

   Table=[Table]

Any help would be greatly appreciated,

Thanks!

ImkeF

Hi ImkeF,

No luck I'm afraid.  This is the code now but I still get an Invalid Identifier error for the Attribute part.

let
myList = List.Generate( ()=>
    [Result = try Query4("https://api.brighttalk.com/v1/channel/10737/webcasts") otherwise null, Counter = 0],
    each [Result] <> null and [Counter] < 5,
    each [Result = try Query4(Table.LastN([Result], 1)[Attribute:href]) otherwise null, Counter = [Counter]+1],
    each [Result])
in
    myList

Hi Anonymous ,

for debugging purposes, please use this query:

let
myList = List.Generate( ()=>
    [Result = Query4("https://api.brighttalk.com/v1/channel/10737/webcasts"), Counter = 0],
    each  [Counter] < 5,
    each [Result =  Query4(Table.LastN([Result], 1)[#"Attribute:href"]), Counter = [Counter]+1],
    each [Result])
in
    myList

It should create a list and there you have to navigate to the errors and paste screenshots of them please.

Hi ImkeF ,

No problem, I used the query and got the following:

Drilling through the first row gives this:

Drilling through on the error rows gives this:

The error is the same for all the subsequent rows...

Hi Anonymous ,

so you have to navigate to the link table first.

Please try this:

let
myList = List.Generate( ()=>
    [Result = Query4("https://api.brighttalk.com/v1/channel/10737/webcasts"), Counter = 0],
    each  [Counter] < 5,
    each [Result =  Query4(Table.LastN([Result]{[Name = "link"]}, 1)[#"Attribute:href"]), Counter = [Counter]+1],
    each [Result])
in
    myList

Hi ImkeF ,

I've tried the code you suggested and although it's thrown an error I think we're making progress!

The first row returns data as expected but the subsequent rows show this error:

Hi Anonymous ,

yes, definitely making progress here.

Please check format of paste picture of myList{0}.

If it is a record, then the following code might work:

let
myList = List.Generate( ()=>
    [Result = Query4("https://api.brighttalk.com/v1/channel/10737/webcasts"), Counter = 0],
    each  [Counter] < 5,
    each [Result =  Query4(Table.LastN([Result][link], 1)[#"Attribute:href"]), Counter = [Counter]+1],
    each [Result])
in
    myList

Hi ImkeF ,

The data in myList{0} looks like this:

Forgive my ignorance as I'm a bit new to Power Query data structures.  Is the structure essentially a table with two rows (records) in it and the Table column then holding a Table data structure within it?

I tried  the ammended code which still errored but gave this message :

Oh yes, I must have been sleeping a bit here.

let
myList = List.Generate( ()=>
    [Result = Query4("https://api.brighttalk.com/v1/channel/10737/webcasts"), Counter = 0],
    each  [Counter] < 5,
    each [Result =  Query4(Table.LastN([Result]{[Name = "link"]}[Table], 1)[#"Attribute:href"]), Counter = [Counter]+1],
    each [Result])
in
    myList

[Result] returns a table

{[Name = "link"]} grabs the row from that table where Name = "link" : This is a record.

[Table] grabs the value from the Table-field of the record. In there sits the table that we're interested in.

Hi ImkeF ,

Ah I see!  I didn't realise that you could nest references in that way!

So, a little more progress I think, still erroring but with this message from row 2 onwards:

Hi Anonymous ,

one day, we'll get there 😉

let
myList = List.Generate( ()=>
    [Result = Query4("https://api.brighttalk.com/v1/channel/10737/webcasts"), Counter = 0],
    each  [Counter] < 5,
    each [Result =  Query4(Table.LastN([Result]{[Name = "link"]}[Table], 1){0}[#"Attribute:href"]), Counter = [Counter]+1],
    each [Result])
in
    myList

Hi ImkeF ,

I think that day has arrived!  We have success!!! 

I now get a result set of Tables as expected:

Can I ask one more question please? Ultimatley I need to replace the Counter variable with a test for whether a link row is returned (the last paginated dataset won't have a link row).  How would it be best to do that?

Many many thanks for all your help with this 😁

Hi Anonymous ,

you can use the "brute force" method you used before (try ... otherwise). Otherwise you'd have to split up your statements: https://www.thebiccountant.com/2020/05/15/miss-last-page-paging-power-bi-power-query/ 

Simply delete the counter statement - that was just for debugging purposes.