Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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.

idtitleadditional columns
IhzTjqXKqkGfO5lrouubHJYADBpSProject A 
54zTjqXKqkGfO5lrouubHJYADB5dProject C 
2350dfadfaswkijsagfldgfahjsgfasgfProject 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?

Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    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

1 Reply

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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