Add Dataverse Team members to SharePoint Person column with Power Automate flow

The use case is to add multiple Dataverse users to a multiselect Person (or Group) column in SharePoint. Dataverse doesn’t support a multiselect Lookup column (to Users table), so I choose to use a lookup column to the Teams table instead. A Power Automate flow is triggered when a new row is added to Dataverse and should get the Team members and add them to the Person or Group column of a new list item in SharePoint. What is the easiest way to do this?

In my blog post ‘Grant access to folder in SharePoint for Access Team members with Flow‘ I show how to get Team members with a List rows action of the hidden teammembership table and an Apply to each to get the domainname of the users. Since the Expand Query option is available in the List rows action of the Dataverse connector it’s “not done” to do an Apply to each action to get additional content from related rows; this should be included in the List rows action by adding an Expand Query. So I tried that.

Looking at the metadata of the Web API, the entity ‘teammembership’ is missing NavigationProperty and NavigationPropertyBinding elements, so the expand option doesn’t work (see Error Details in image above).

Reading through the documentation about the Web API I came across the section on the use of Lambda operators. Now in one of the examples to Filter results based on values of collection-valued navigation properties the ‘teammembership_association’ is used to retrieve systemuser records who are also administrators of a team whose name is specified. But would it also work for team membership?

So I modified the filter to get the team members of a specified teamid. And it works!

Now the input for the User column in SharePoint should be of the format:

[
  {
    "Claims": "i:0#.f|membership|alans@CRM200770.OnMicrosoft.com"
  },
  {
    "Claims": "i:0#.f|membership|aliciat@CRM200770.OnMicrosoft.com"
  },
  {
    "Claims": "i:0#.f|membership|allieb@CRM200770.OnMicrosoft.com"
  }
]

I can use a Select action to compose this format.

Expression to set the claims format for the user:

concat('i:0#.f|membership|',item()?['domainname'])

This will convert the output of the List rows action to the format for the User column.

The output of the Select action can be added to the Person column in the Create item action.

The created items in SharePoint show the members of the selected Team in Dataverse.

So in this way we can avoid using the Apply to each action, even if expand is not an option.