Dynamically query multiple OData Feeds

[mattgrif]

I am trying to create a master view of all tasks assigned to multiple Microsoft Planner Plans in a single Group. This is possible using the Microsoft 365 Graph, but I want it to dynamically add additional plans to the dashboard I'm creating so I don't have to keep adding additional feeds.

I'm able to pull all Plan ID's into a single list using the below M Code

let
Source = OData.Feed("https://graph.microsoft.com/v1.0/groups/{GroupId}/planner/plans", null, [Implementation="2.0"]),
#"Removed Other Columns" = Table.SelectColumns(Source,{"id"}),
id = #"Removed Other Columns"[id]
in
id

I need help writing the code for hitting the endpoint https://graph.microsoft.com/v1.0/planner/plans/{PlanId}/tasks where PlanId is provided from my list.

[v-lionel-msft]

Hi @mattgrif ,

Isn't it the same as above？

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mattgrif]

I tried this and I get the error "We encountered an error while trying to connect.

Details: "Query 'Query2' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

The only difference in my setup vs yours is the Managed Parameter is a Query of the m code in the first screenshot.

[lbendlin]

https://duckduckgo.com/?q=power+query+formula+firewall&ia=web

[lbendlin · Accepted solution]

you can create a function in M that takes the PlanId as a parameter and does the fetching.  or just concatenate the string.

Table.AddColumn("x","y", each OData.Feed(urla & [PlanId] & urlb)  )

etc.

[mattgrif]

I tried adding a column and it worked but listed as Table. When I try to expand the table I get the error "Column 'id' in Table 'New GTAC Plans' contains a duplicate value '{APlanID}' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table"

let
Source = OData.Feed("https://graph.microsoft.com/v1.0//groups/{GroupIDRemoved}/planner/plans", null, [Implementation="2.0"]),
#"Removed Columns" = Table.RemoveColumns(Source,{"tasks", "buckets", "details"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Tasks", each OData.Feed("https://graph.microsoft.com/v1.0/planner/plans/" &[id]&"/tasks", null, [Implementation="2.0"])),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"createdBy", "createdDateTime", "owner"}),
#"Expanded Tasks" = Table.ExpandTableColumn(#"Removed Columns1", "Tasks", {"id", "title", "startDateTime", "createdDateTime", "dueDateTime"}, {"Tasks.id", "Tasks.title", "Tasks.startDateTime", "Tasks.createdDateTime", "Tasks.dueDateTime"})
in
#"Expanded Tasks"