Forum Discussion

mattgrif's avatar
mattgrif
Regular Visitor
6 years ago
Solved

Dynamically query multiple OData Feeds

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.

Need Help
  • lbendlin's avatar
    lbendlin
    6 years ago

    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.

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    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's avatar
      mattgrif
      Regular Visitor
      6 years ago

      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"

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    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's avatar
      mattgrif
      Regular Visitor
      6 years ago

      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.