none
Pagination API until no results RRS feed

  • Question

  • I have this API call. There <g class="gr_ gr_80 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Grammar multiReplace" data-gr-id="80" id="80">is</g> pagination and a counter. But Ideally, I would like it to stop whenever there's no results left. I've tried a couple of things and I guess it needs to be done during the List.Generate where I currently have each [Counter] < 50. But I`m not sure what to put there. I.e. each [WebCall] <> null ?

     

    This is the code I have

    Let
            Pagination = List.Skip(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value      
       	
            each [Counter]<50,
            each [ WebCall = Function.InvokeAfter(
                ()=>Json.Document(Web.Contents(Url, 
                    
                    [Query=[limit="" & Limit & "",page="" & Text.From([Page]) & "", #"filter[1][attribute]"="created_at", #"filter[1][gt]"=StartDate],
                    Headers=[Authorization="OAuth 
                        oauth_consumer_key=" & consumerKey & ", 
                        oauth_token=" & Token & ", 
                        oauth_signature_method=" & SignatureMethod  & ", 
                        oauth_timestamp=" & TimeStamp  & ", 
                        oauth_nonce=" & Nonce & ", 
                        oauth_version=""1.0"", 
                        oauth_signature=" & Signature & "
                    "]
                ])), 
                #duration(0,0,0,0.5)),          
                Page = [Page]+1,
                Counter = [Counter]+1  
    	]
    	) ,1),
        Table = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
       column = Table.ExpandRecordColumn(Table, "Column1", {"WebCall"}, {"Column1.WebCall"}),
    
    // convert EACH record to the table of columns Name and Value^
        RecordsToTables = Table.TransformColumns(
            column, 
            {"Column1.WebCall", Record.ToTable}
            ),
    // expand these tables
        ExpandTables = Table.ExpandTableColumn(RecordsToTables, "Column1.WebCall", {"Name", "Value"}, {"WebCall.Id", "Value"}),
    // get all field names of all inner records of Value column:
        Recs = List.Buffer(ExpandTables[Value]), 
        FieldNames = List.Union(List.Transform(Recs, Record.FieldNames)),
    // expand these records with the full list of field names:
        ExpandRecords = Table.ExpandRecordColumn(ExpandTables, "Value", FieldNames, FieldNames),
    
    in
        ExpandRecords 

    I guess I need to use something like Record<g class="gr_ gr_123 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style replaceWithoutSep" data-gr-id="123" id="123">.Count</g>. Ie in addition to each [Counter]<50, I can use each [Counter]<50 or Record.Count[WebCall] > 1, . <g class="gr_ gr_351 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" data-gr-id="351" id="351">Obviously</g> that doesn't work but I`m not sure how to check if the multiple records within the WebCall are empty or not.

    Friday, April 5, 2019 1:44 AM

Answers

  • Yes, that's not uncommon and easy to tackle. Just add another record that stores the previous Webcall and compare both instead in the condition-step:

    Let Pagination = List.Skip(List.Generate( () => [WebCall=[], PrevWebCall=[1], Page = 1, Counter=0], // Start Value each [Counter]<50 and [WebCall] <> [PrevWebCall], each [ WebCall = Function.InvokeAfter( ()=>Json.Document(Web.Contents(Url, [Query=[limit="" & Limit & "",page="" & Text.From([Page]) & "", #"filter[1][attribute]"="created_at", #"filter[1][gt]"=StartDate], Headers=[Authorization="OAuth oauth_consumer_key=" & consumerKey & ", oauth_token=" & Token & ", oauth_signature_method=" & SignatureMethod & ", oauth_timestamp=" & TimeStamp & ", oauth_nonce=" & Nonce & ", oauth_version=""1.0"", oauth_signature=" & Signature & " "] ])), #duration(0,0,0,0.5)), Page = [Page]+1, Counter = [Counter]+1,

    PrevWebCall = [WebCall] ] ) ,1),




    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by aukevv Friday, April 5, 2019 4:51 PM
    Friday, April 5, 2019 4:30 PM
    Moderator

All replies

  • You have to include this row:

     each [Counter]<50,

    to sth like this: each [Counter] < 50 and [WebCAll] <> null

    … if it is actually null that is returned.

    You might also adjust your initial record from the first row to contain a value.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Friday, April 5, 2019 10:44 AM
    Moderator
  • I've tried that but it doesn't seem to work. I ran some tests and it seems the WebCall results are not empty even when there's no results from the API. What I noticed is that the existing results somehow get duplicated when there's no new results. It seems that when using pagination on the Magento Rest API it just repeats the previous page when there's no results. I.e. when there's 200 results, limit=100&page=3 will give the same results as page=2.

    Do you think I`m out of luck here or is there a way to check if an id within [WebCall] already exists and stop based on that?

    Friday, April 5, 2019 4:24 PM
  • Yes, that's not uncommon and easy to tackle. Just add another record that stores the previous Webcall and compare both instead in the condition-step:

    Let Pagination = List.Skip(List.Generate( () => [WebCall=[], PrevWebCall=[1], Page = 1, Counter=0], // Start Value each [Counter]<50 and [WebCall] <> [PrevWebCall], each [ WebCall = Function.InvokeAfter( ()=>Json.Document(Web.Contents(Url, [Query=[limit="" & Limit & "",page="" & Text.From([Page]) & "", #"filter[1][attribute]"="created_at", #"filter[1][gt]"=StartDate], Headers=[Authorization="OAuth oauth_consumer_key=" & consumerKey & ", oauth_token=" & Token & ", oauth_signature_method=" & SignatureMethod & ", oauth_timestamp=" & TimeStamp & ", oauth_nonce=" & Nonce & ", oauth_version=""1.0"", oauth_signature=" & Signature & " "] ])), #duration(0,0,0,0.5)), Page = [Page]+1, Counter = [Counter]+1,

    PrevWebCall = [WebCall] ] ) ,1),




    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by aukevv Friday, April 5, 2019 4:51 PM
    Friday, April 5, 2019 4:30 PM
    Moderator
  • Thanks. It seems though that this bit:

    each [Counter]<50 and [WebCall] <> [PrevWebCall],

    Gives me an error

    Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

    Code:

            Pagination = List.Skip(List.Generate( () => [WebCall=[], PrevWebCall=[1], Page=1, Counter=0], // Start Value   
       	
            each [Counter]<50 and [WebCall] <> [PrevWebCall],
            each [WebCall = Function.InvokeAfter(
                ()=>Json.Document(Web.Contents(Url, 
                    
                    [Query=[order="" & SortingOrder & "",dir="" & AscDsc & "",limit="" & Limit & "",page="" & Text.From([Page]) & "", #"filter[1][attribute]"="created_at", #"filter[1][gt]"=StartDate],
                    Headers=[Authorization="OAuth 
                        oauth_consumer_key=" & consumerKey & ", 
                        oauth_token=" & Token & ", 
                        oauth_signature_method=" & SignatureMethod  & ", 
                        oauth_timestamp=" & TimeStamp  & ", 
                        oauth_nonce=" & Nonce & ", 
                        oauth_version=""1.0"", 
                        oauth_signature=" & Signature & "
                    "]
                ])), 
                #duration(0,0,0,0.0000001)),          
                Page = [Page]+1,
                Counter = [Counter]+1,
                PrevWebCall = [WebCall]  
    	]
    	) ,1),

    Friday, April 5, 2019 4:46 PM
  • Ok, I seem to have fixed that by changing PrevWebCall=[1] to PrevWebcall=1. Not sure why that would make a difference though.
    Friday, April 5, 2019 4:51 PM