@AnnetteM The logic of your flow needs to be adjusted—you're on the right track. For complex flows I find it easier to start at the end when trying to figure out the logic. In your example, the end result is needing to update your Provider List's Credential Column with the values based on the staff selected. You'll need to also consider how you will handle updating your list when the staff are updated. However, let's keep it simple for now and start with your initial requirements.
Tip: Rename your actions to keep things organized. This especially important when building a more complex flow that requires multiple instances of the same action. Run tests often to troubleshoot early! For more flow troubleshooting tips—check out This YT Tutorial: 5 Power Automate Troubleshooting FAQs and Helpful Tips for Creating Better Flows
Get Providers
The end of your flow will need to include an Apply to each action which loops through each Provider to update the credential column. With that in mind, you will need to first get a list of Providers you want to loop through. You've stated that you want to filter out your list where the Credentials column is empty.
Add a Get Items action. For my demo I'll be using an existing list. In my case I'll be using the Department column. For the Filter Query you'll need to use the internal column name of your Credentials column. If you aren’t sure how to get this, please refer to this section of one of my YT tutorials.
Whenever I use a Filter Query in a Get Items action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow.
Insert a Compose action. Add an Expression. Use the length() function.
Select the Dynamic content tab and insert the value dynamic content from the Get Items action into the length() function.
Run a test. Verify the output of the Compose action is what you expect.
Get Staff
Add another Get Items action to get the Employees. Best practice is to use the Filter Query whenever you are using the Get Items action to reduce the number of items returned. Also, your flow requires that staff have a Provider selected from a look up column.
Add a Filter query to filter out staff that don't have an empty Provider column—in my case, my look up column is Branch.
As above, add a Compose action to store the count of staff. This isn't necessary to make your flow work—however, it can be helpful to troubleshoot your flow. Note: This is why it's important to rename your actions. It makes it easier to identify which dynamic content belongs to which actions.
Run a test. Verify the Compose action is outputting the correct number of staff..
It's important to note you only want to run the Get Items actions once on a list in a flow. Running this action multiple times—especially inside an Apply to Each action is inefficient. Instead, you want to utilize a Filter Array action. To learn more about how to use the Filter Array action, please refer to this recent YT Tutorial I uploaded.
Select Employees
Add a Select action to your flow. We'll use this action to select the Provider (in my case Branch Value) and Credential (in my case Department Value).
Run a test. Review the output. You can add as many keys and values to the Select action that you need. This reduces the data being processed to only the data your require in your flow.
I'm also assuming that there is only a single credential selected for each staff. If not, then this action will need to be adjusted. I only have a single department selected for each employee in my case.
Loop through Each Provider
Add an Apply to Each action to loop through each provider. Ensure you are selecting the proper value dynamic content from the correct action.
This next step is optional—however I recommend it for troubleshooting purposes. Add a Compose action to display the current Provider (in my case Branch) being looped through. Again, ensure you are selecting the proper dynamic content from the correct action. In my case, I'll use the Title column.
Get the Staff for the Current Provider
Add a Filter Array action to your flow. In the From field, insert the output from the Select action.
In the first value field, you'll need to use an expression. Start with:
item()?['']
In between the single quotes you need to enter the dynamic content key. Reference the key that was entered for the Provider in the Select action. The dynamic content key is case sensitive. In my case it's Branch.
In the second value field, insert the output from the Compose action above. Alternatively, if you didn't use the Compose action you can insert the appropriate dynamic content into the second value field.
The Filter Array action is filtering out the items from the Select action where the Provider (branch in my case) is equal to the current Provider (branch) being looped through.
Just as we've done above with the Get Items action, add a Compose action to store the count from the Filter Array action. Add an Expression. Use the length() function.
Select the Dynamic content tab and insert the body dynamic content from the Filter Array action into the length() function.
Run a test. Review the outputs. This is why I find the Compose action helpful. The Apply to Each action doesn't state the current item being looped through. It makes it easier to review the outputs when I know what the Apply to Each action is looping through.
In SharePoint you can group your list by Credentials (you can only do this if it's a single choice selection). Tip: Display the count for the column.
This makes it easy to verify if the output from your flow is correct.
Condition Check
Add a Condition action to your flow. The purpose of this action is to check whether the Filter Array action has returned any Staff. If not, then the flow doesn't do anything. If so, it can continue on with the rest of the actions.
In the first value field insert the output from the Compose action that outputs the number of staff for the current provider being looped through.
Change the operator to is not equal to. In the second value field insert 0.
Select Credentials
Take a look at the output of the Filter Array action. You'll need the dynamic content key for the next action as the dynamic content from a filter array action (most times) cannot be selected—an expression must be used. Refer to this section of a YT Tutorial I uploaded on how to get dynamic content from a Filter Array action.
Add a Select action to the Yes branch. In the From field, insert the body from the Filter Array action.
Click on this icon to change from key value to map mode.
Insert an expression. Start with this:
item()?['']
Insert the dynamic content key between the single quotes. In my case it's Credential. In your case it maybe different—get the key from the Filter Array output. The dynamic content key is case sensitive.
Run a test. You can't see it in the Select action which is why I copy/pasted the output into a text file. Since the Credential column is a multi-choice column, the Select action output is arrays nested inside an array. We'll need to flatten this content so it's a single array of credentials.
Flatten the Array
Add a Compose action to convert the array into JSON format. Replace [insert select output] with the output from the Select action.
{
"Root": {
"Items": [insert select output]}
}
}
Run a test.
Next add another Compose action. We'll use this action to convert the JSON to XML.
Use the xml() function.
Click on the Dynamic content tab and insert the output from the Compose action above.
Wrap the xml() function in an xpath() function. Go to the start of the expression by pressing the up arrow key. Enter xpath with an opening parenthesis.
Go to the end of the expression by pressing the down arrow key. Enter a comma and single quotes.
Between the single quotes enter:
//*[local-name()="Value"]/text()
Don't forget to close off the expression by entering a closing parenthesis.
The xpath() function searches the XML for all elements that have the tag "Value". It extracts the value content.
Run a test. The Compose action should return an array of all credentials.
Unique List
Next you'll need to return a unique list of credentials. Insert an expression and use the union() function.
The union function combines two arrays/collections into one while removing the duplicates. Click on the Dynamic Content tab and insert the output from the Compose action above.
Add a comma. Tip: The tooltip will indicate which parameter of the expression you are defining.
Insert the output from the Compose action above again. The expression should look like this (may differ if your flow depending on how you've named your Compose action)
Run a test. Review the outputs of the Compose action. It should contain unique values only.
Group Actions (optional)
Add a Scope action to your flow. I like using Scope actions in my flow to group actions together to keep my flow organized. It also allows me to quickly collapse multiple actions with a single click and reduces the vertical space the actions take up in a flow.
Prepare the Multi-Choice Values
You'll need to prepare the values to patch to your SP List. Add a Select action to your flow. Insert the Output from the compose action above (the one with the array of unique credentials) into the From field.
In the key field, insert the word Value with a capital V. For the value field, insert an expression. Use the item() function to return each array item.
Run a test. Review the output an ensure the array is in the proper format.
Update Item
Add an Update Item action. Ensure you are inserting the ID dynamic content from the proper action. Remember that you are currently looping through each Provider (branch in my case).
You'll need to insert the appropriate dynamic content for any required fields. In this case, the title is required. Again, pay attention to the action your are inserting dynamic content from. You have two Get Items actions in your flow (this is why it's important to rename your actions!
For the Credentials column, click on this icon to input the entire array. The array in this case is in the output of the Select action.
Again, you will need to verify that you are selecting the proper output. Note the names of your actions.
Run a test. Verify that the credentials are appearing in your SP list.
Hope this helps!
For more flow troubleshooting tips—check out this YT Tutorial: 5 Power Automate Troubleshooting FAQs and Helpful Tips for Creating Better Flows
In this tutorial I cover:
✅ How to troubleshoot a false Condition action result
✅ How to get dynamic content when it isn’t selectable from the list of dynamic content
✅ How to troubleshoot an Apply to Each action that isn’t looping through
✅ How to troubleshoot a skipped Apply to Each action
✅ How to troubleshoot a Filter Query
✅ How to use a SharePoint yes/no column in a Filter Query
✅ How to use Compose actions to troubleshoot a Power Automate flow
✅ How to troubleshoot multiple emails being sent
✅ How to troubleshoot multiple Teams messages being sent
---
You might find this YT Tutorial I recently uploaded helpful: 3 Mistakes YOU 🫵 are Making with the Apply to Each Action in your Microsoft Power Automate Flow
In this video tutorial I’ll go over how to avoid these common mistakes when using the Apply to Each action in a Power Automate flow:
1️⃣Looping through a Single Item
2️⃣ Creating Unnecessary Nested Loops
3️⃣ Looping through an Unfiltered Array
At the end of the video I share a few helpful insights when it comes to using the Apply to Each action in your flow.
I'll also cover:
✅ How to avoid the Apply to Each action with a single item array
✅ How to use the item() function to access dynamic content in an array
✅ How to prevent unnecessary nested Apply to Each action loops
✅ How to use the Select action
✅ How to convert an array to a string with the Select action
✅How to use the Filter Query field
✅ How to count the number of items in an array
✅ How to use a condition control
✅ How to use the concurrency control
✅ How to set a top count
✅ How to use Compose actions for troubleshooting
