cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adamc88
New Member

Unique rows in Excel based off a SharePoint list?

Hi everyone,

 

I have a SharePoint list and an Excel table on SharePoint Online. I want to compare the entries on the list with those on the Excel table. Any entries on the list which don't appear on the table need to be added onto the table.

 

What's the best way to go about this? I've tried using 'Apply to each' loops but I'm clearly implementing it incorrectly as each subsequent record is added an additional time. In other words, entry #2 is added twice and entry #3 is added three times.

 

I've included a screenshot of what I've built. Preceding the screenshot is a schedule trigger, the 'Get items' step and the 'List rows present on a table' step. I've taken the 'Filter array' expression from another post on here hoping it'd work, but when 'true' it returns the entire array, not the record I'm trying to filter on by email address. I'll admit it was a hail Mary as I don't fully understand how to use it.

 

Any help would be greatly appreciated.

 

FLow.png

1 ACCEPTED SOLUTION

Accepted Solutions
chrissahagun
Resolver II
Resolver II

Mine worked... see screenshot below.  Please see to it that each dynamic content input to each step comes from the correct source (take note the icons and also the sub-headers in the dynamic content menu)...

 

chrissahagun_0-1616205659149.png

 

Although, if you need your automation running as soon as possible, then try taking out your filter array and use your nested loop.  then just explore making it more efficient next time.

View solution in original post

5 REPLIES 5
chrissahagun
Resolver II
Resolver II

I don't think you need to use another Apply to each.  One should already do.

 

1. Apply to each to loop through the SP List -> you did this already

 

2. Go directly to filter array on each SP List Value:

       List.Email = Excel.Email

       (do a trim on both sides if you want to be sure you don't have spaces around)

 

3. Do your condition as you already did (I assume if the filter array result is empty)

       Yes -> create a row on the excel table

       No -> whatever needs to happen otherwise.

 

Reason for this is that the filter array is actually a more efficient way to do what a second loop does.  A second loop could work, but without a filter array.  a filter array could do, but without the second loop. something like that.

 

I think 😄

 

P.S. I'm also in the process of learning MS Flow.  Me mentor's suggestion is try to answer questions in communities so I can see real life problems and be forced to do research and real scenario POCs.  So I appreciate some confirmation if my response worked or not.  I'm looking forward to know! 😉 

adamc88
New Member

Thanks for the reply, and I appreciate the help!

 

Unfortunately this doesn't work. When I try to add 'Excel.Email', Power Automate automatically adds a second "Apply to each" loop, I assume as it needs to traverse the Excel table to compare to the values from the SharePoint list.

 

I'll keep poking at it next week. If I figure it out I'll post an update here. In the meantime, I'm open to any further assistance as this is a right head-scratcher for me.

chrissahagun
Resolver II
Resolver II

Mine worked... see screenshot below.  Please see to it that each dynamic content input to each step comes from the correct source (take note the icons and also the sub-headers in the dynamic content menu)...

 

chrissahagun_0-1616205659149.png

 

Although, if you need your automation running as soon as possible, then try taking out your filter array and use your nested loop.  then just explore making it more efficient next time.

View solution in original post

adamc88
New Member

I deleted the entire flow, rebuilt it from scratch and it now works following your guidance. I was putting the correct values in, however whenever I selected "values" from Excel's 'get rows...' step in the 'Filter array' step, it popped a new "Apply to each" loop in there. Not sure what was going on, but a clean slate seems to have resolved it and it's behaving properly now - and working as I need it to!

 

Thank you for your help! 🤓

chrissahagun
Resolver II
Resolver II

Awesome!

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,958)