cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danielntamasi
Frequent Visitor

Recursive query with changing query parameter based on each iteration

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

1 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

 

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors