Combine multiple odata calls based on id's in a table

[Anonymous]

I've been trying to get data from the Microsoft Graph to aggregate information of different MS Planner boards in an report, but i need to do it in a dynamic way so that i don't have to update queries if a new plan is added to the group.

In O365 Planner is connected to a group. We can get all Plan boards connected to a group by calling the graph with the group id:

let
    Source = OData.Feed("https://graph.microsoft.com/v1.0/groups/5918c48f-3139-4939-b6d2-362793f37632/planner/plans", null, [Implementation="2.0"])
in
    Source

This results in a table with a record for each group containing (amongst others) the Plan board id and the plan board title.

id	title	additional columns
IhzTjqXKqkGfO5lrouubHJYADBpS	Project A
54zTjqXKqkGfO5lrouubHJYADB5d	Project C
2350dfadfaswkijsagfldgfahjsgfasgf	Project G

Now i want to create a table with all the tasks beloging to these projects. In order to do this i need to query the graph again for each Plan Board and supply the id. This is a query that needs to be fired for every plan id

let
    PlanTasks = OData.Feed("https://graph.microsoft.com/v1.0/planner/plans/[THIS IS THE PLAN ID:IhzTjqXKqkGfO5lrouubHJYADBpS]/tasks/", null, [Implementation="2.0"])
in
    PlanTasks

What i can't figure out is how to loop through the results of the first call and use these id's to make the calls to get all the tasks and combine them in a single new table. Does anyone know how to do this?

[Jimmy801 · Accepted solution]

Hello @Anonymous 

you need a custom function that takes your ID as parameter and then invoking the function in your main table in a new column. I don't know the api, but your function could be something like this (copy this code to a blank query and name it fnGetData

(id)=>
let
    PlanTasks = OData.Feed("https://graph.microsoft.com/v1.0/planner/plans/[THIS IS THE PLAN ID:" & id & "]/tasks/", null, [Implementation="2.0"])
in
    PlanTasks

In your main table, add a new column invoking to function with fnGetData([id])

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy