Connecting Sharepoint List to Excel Contact sheet

[ShaunC]

We have created a Power Automate to intake collateral requests from our sales teams. The intake app collects the requestor's name in a SharePoint List, and from that, I am trying to reference a Contact list we have stored in an Excel spreadsheet to find the Procare Marketer (column name PMT) they are assigned to. I then need to reference that PMT name in the same Excel contact list to get their email address to send a notification to.

The challenge I am coming to is later when trying to reference the excel row, it does not show in the dynamic content. I am unsure how to reference this output to then use it later in my flow.

I am sure an expression is required, but with my limited experience, I am unsure what to type to get my desired result.

[creativeopinion]

@ShaunC Can you show a full screenshot of your flow. From what I can see initially—it looks like your Compose action is outside of the Apply to Each action where the Get a row action is nested inside. This is why you can't access the dynamic content. 

I think you'll probably want to use a List Rows Present in a table action instead and a Filter Array action to find the row in your excel table. You'll want to insert this action in the root of your flow and run it once. 

To learn more about how to use the Filter Array action, please refer to this YT Tutorial: Are you using the Microsoft Power Automate Filter Action wrong?

In this video tutorial I’ll show you 3 practical ways to use the Filter Array action and how to use it properly.

1️⃣ Cross-Referencing Data

2️⃣ Filtering by Key

3️⃣ Substring Matching

Did you know that the Condition action has a limit of 10 conditions? Although it might look like the Filter Array action can only accept one condition—this is not true. By using the advanced mode you can enter multiple conditions into a Filter Array action with an expression.

IN THIS VIDEO:

✅ 3 Ways to Use the Filter Array Action

✅ How to use the Scope Action to Group Actions

✅ How to Check the Number of Items returned from a Filter Array Action

✅ How to Cross-Reference Data in Excel with a SharePoint List

✅ How the Filter Array Action Works

✅ How to Access the Dynamic Content from a Filter Array Action

✅ How to Filter Items by a Key

✅ How to Filter Items by Matching a Substring

✅ How to Use Multiple Conditions in a Filter Array Action

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! If you're feeling generous— ☕️  Buy me a coffee: https://www.buymeacoffee.com/acreativeopinion

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

[ShaunC]

Thank you, @creativeopinion. You are correct, I was placing the compose outside of the Apply to Each. I watched your filter array video prior but was unable to put 2 and 2 together until your comment. Having rewatched it, I think I better understand now and will rebuild my flow to use a filter array on the List Rows Present. This is actually how I initially started, but was having an issue getting the row I needed as the contact list is rather large and contains several columns which I think was hitting the limit.

While I don't have a solution yet, this gets me much closer. I will follow up if I get stuck again!

[creativeopinion]

@ShaunC In case someone else has an issue similar to yours, it might be helpful for them to see how to troubleshoot the issue you are having. 

Since your Filter Array action is not returning any items you'll need to troubleshoot that. Whenever you are using dynamic content in a Filter Array or Condition action, it's always a good idea to Compose the dynamic content before using it in your Filter Array or Condition action.

This is especially important when you are using the is equal to operator. When using this operator it's looking for an exact match—it's also case sensitive!

Run a test and review the output of the Compose action storing the Requester Name. Can you confirm that there is a row or rows in your Excel table where the Full Name cell matches the Requestor Name?

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

[ShaunC]

@creativeopinion I can confirm that the name does exist in the Excel "Full Name" column:

I have also verified that there are no extra spaces and the case sensitivity matches.

But still no results.

I did test with the first name in the list "Aaron Constantino" and did get a result in the output of the Filter Array.

This leads me to believe I am hitting a limit in the List Rows Present in a Table function. I am unsure how to:

a) increase this limitation

b) reduce the column details as I only really need Full name, Email Address, and PMT columns for this flow.

 

[ShaunC]

@creativeopinion To further back my test, I used the get a row from a table function and confirmed the data does exist in the Excel table (sorry it is so small).

[creativeopinion · Accepted solution]

@ShaunC You can toggle on Pagination in the List Rows present in a table action and set a threshold.

[ShaunC]

AHHHHHH @creativeopinion. This was the ticket! I think from here I can use all the methods we have gone through to complete the flow. I will post when the flow is complete and will also accept your solution when finished. Thank you for your help.

[creativeopinion]

@ShaunC Great! Sounds good. Don't hesitate to reach out if you get stuck.