Recursive query with changing query parameter based on each iteration

[danielntamasi]

Hi,

I have an API that i need to call multiple (unknown) number of times to get a complete dataset.

Here is what I need to achieve:
1) get data (20 rows each time),

2) update parameter (olderThanID) each iterations;

3) rerun with the new parameter and continue rerunning until countOfPosts <> 20

4) append all of these into tables into one table

I have the code successfully running until the countOfPosts <> 20 but I can't get these to append into one table. Also, I only see the last table not all of the tables.

let
    getYammerPosts = (optional oldestID as text) as table=>
        let
            listofPosts = Json.Document(Web.Contents("https://www.yammer.com/api/v1/messages/in_group/group_id.json" & oldestID, [Headers=[Authorization="API_token"]])),
            #"Converted to Table" = Record.ToTable(listofPosts),
            Value = #"Converted to Table"{1}[Value],
            #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "sender_id", "replied_to_id", "created_at", "url", "body", "thread_id", "client_type", "attachments", "liked_by"}, {"id", "sender_id", "replied_to_id", "created_at", "url", "body", "thread_id", "client_type", "attachments", "liked_by"}),
            #"Expanded liked_by" = Table.ExpandRecordColumn(#"Expanded Column1", "liked_by", {"count"}, {"count"}),
            #"Expanded body" = Table.ExpandRecordColumn(#"Expanded liked_by", "body", {"plain"}, {"plain"}),
            #"Expanded attachments" = Table.ExpandListColumn(#"Expanded body", "attachments"),
            #"Expanded attachments1" = Table.ExpandRecordColumn(#"Expanded attachments", "attachments", {"type", "description", "comment"}, {"type", "description", "comment"}),
            #"Added Conditional Column" = Table.AddColumn(#"Expanded attachments1", "Body", each if [type] = "praise" then [description] & "
        " & [comment] else [plain]),
            #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"type", "description", "comment", "plain"}),
            #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"id"}),
            countOfPosts = Table.RowCount(#"Removed Duplicates"),
            idFind = Table.Min(#"Removed Duplicates","id"),
            olderThanID = "?older_than=" & Number.ToText(idFind[id]),
            recursive = 
                if countOfPosts = 20
                then @getYammerPosts(olderThanID)
                else #"Removed Duplicates"
        in
            recursive
in
    getYammerPosts

 Your help is greatly appreciated :).

Best,

Daniel

[v-shex-msft]

Hi @danielntamasi ,

You can try to use the following query if it meets your requirements. (I modify your code to add an optional table parameter to pass previous table result and do combine operation in 'distinct steps')

let
    getYammerPosts = (optional oldestID as text, optional tb as table) as table=>
        let
            listofPosts = Json.Document(Web.Contents("https://www.yammer.com/api/v1/messages/in_group/group_id.json" & oldestID, [Headers=[Authorization="API_token"]])),
            #"Converted to Table" = Table.FromList(Record.ToTable(listofPosts){1}[Value], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "sender_id", "replied_to_id", "created_at", "url", "body", "thread_id", "client_type", "attachments", "liked_by"}, {"id", "sender_id", "replied_to_id", "created_at", "url", "body", "thread_id", "client_type", "attachments", "liked_by"}),
            #"Expanded liked_by" = Table.ExpandRecordColumn(#"Expanded Column1", "liked_by", {"count"}, {"count"}),
            #"Expanded body" = Table.ExpandRecordColumn(#"Expanded liked_by", "body", {"plain"}, {"plain"}),
            #"Expanded attachments" = Table.ExpandListColumn(#"Expanded body", "attachments"),
            #"Expanded attachments1" = Table.ExpandRecordColumn(#"Expanded attachments", "attachments", {"type", "description", "comment"}, {"type", "description", "comment"}),
            #"Added Conditional Column" = Table.AddColumn(#"Expanded attachments1", "Body", each if [type] = "praise" then [description] & "
        " & [comment] else [plain]),
            #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"type", "description", "comment", "plain"}),
            result = if tb<> null then Table.Distinct(Table.Combine({#"Removed Columns",tb})) else Table.Distinct(#"Removed Columns", {"id"}),
            countOfPosts = Table.RowCount(result),
            idFind = Table.Min(result,"id"),
            olderThanID = "?older_than=" & Number.ToText(idFind[id]),
            recursive = 
                if countOfPosts = 20
                then @getYammerPosts(olderThanID,result)
                else result
        in
            recursive
in
    getYammerPosts

Regards,

Xiaoxin Sheng

[danielntamasi · Accepted solution]

Xiaoxin,

Thank you for this great help! It almost worked for me perfectly!

Couple of things i needed to stich up at the end:

- moved 'removed duplication' in front of the combine command (there aren't any duplication across the tables as the duplications were introduced by me as part of an earlier command when i expanded a column with a list of values);

- needed to modify the countOfPosts command a bit as it counts the whole table now therefore table entries are the multiples of 20, not just 20.

- (i've also introduced one last check in the if statement that aims the last ID known to me to be checked. This avoid infinite loops when even the last table has 20 entries (1 out of 20 times, statistically speaking).

Again, thanks for this great help, it's much appreciated!

let
    getYammerPosts = (optional oldestID as text, optional tb as table) as table=>
        let
            listofPosts = Json.Document(Web.Contents("https://www.yammer.com/api/v1/messages/in_group/group_id.json" & oldestID, [Headers=[Authorization="API_token"]])),
            #"Converted to Table" = Table.FromList(Record.ToTable(listofPosts){1}[Value], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "sender_id", "replied_to_id", "created_at", "url", "body", "thread_id", "client_type", "attachments", "liked_by"}, {"id", "sender_id", "replied_to_id", "created_at", "url", "body", "thread_id", "client_type", "attachments", "liked_by"}),
            #"Expanded liked_by" = Table.ExpandRecordColumn(#"Expanded Column1", "liked_by", {"count"}, {"count"}),
            #"Expanded body" = Table.ExpandRecordColumn(#"Expanded liked_by", "body", {"plain"}, {"plain"}),
            #"Expanded attachments" = Table.ExpandListColumn(#"Expanded body", "attachments"),
            #"Expanded attachments1" = Table.ExpandRecordColumn(#"Expanded attachments", "attachments", {"type", "description", "comment"}, {"type", "description", "comment"}),
            #"Added Conditional Column" = Table.AddColumn(#"Expanded attachments1", "Body", each if [type] = "praise" then [description] & "
        " & [comment] else [plain]),
            #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"type", "description", "comment", "plain"}),
            #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"id"}),
            result = if tb<> null then Table.Combine({#"Removed Duplicates",tb}) else #"Removed Duplicates",
            countOfPosts = Table.RowCount(result),
            idFind = Table.Min(result,"id"),
            olderThanID = "?older_than=" & Number.ToText(idFind[id]),
            recursive = 
                if Number.Mod(countOfPosts, 20) = 0 and idFind[id] <> last_known_id
                then @getYammerPosts(olderThanID,result)
                else result
        in
            recursive
in
    getYammerPosts