Find unique rows, update fields, then delete duplicates

[GaryH]

Hello folks,

Complete PA newbie here and I spent longer than I care to mention trying to work this out on my own, so I am coming to you feeling a little defeated 😅. I'm close, but there's one part that I'm stumped on and I suspect it is very straightforward to someone that has a basic understanding of what these objects are doing.

Scenario:

I have a SharePoint list that is populated regularly with a complete list of stories that will be pushed in the next fix version. This means there is a lot of duplicate handling, but it's a little more complex.

After they are imported I need to:

1. Check the list for unique keys (i.e. things there were included in a fix version but have been removed)
2. Update the fix version field for these unique rows to prompt checking
3. After the fix versions have been updated, delete the most recent duplicates

My starting point was the very helpful Find and Remove Duplicates thread by @takolota, but I have been having trouble iterating on it.

Test data:

Fix Version	Project	Key
2301	A	A1
2301	A	A1
2301	B	B1
2301	B	B1
2301	B	B2

Expected output:

Fix Version	Project	Key
2301	A	A1
2301	B	B1
CHECK	B	B2

Actual Output:

Fix Version	Project	Key
CHECK	A	A1
CHECK	B	B1
CHECK	B	B2

Flow:

Here's where I suspect the trouble occurs:

ID: items('Apply_to_each')?['ID']

Key: items('Apply_to_each')?[title] (Sharepoint's default "title" column was renamed "key", I've tried both.)

If I use Dynamic Values for ID and Key, I get the same output but an additional Apply to each loop is added to my flow within the one above.

Thanks for your time!

/Gary

[Matthy79]

Hello @GaryH 

If you really only have an array like your sample array, “union” will do the work. Just add a compose action with this formular: “union(yourArray, yourArray)”

Instead of yourArray you have to put in the output of your array.

I didn’t read the other topic, but I’m pretty sure, they are doing something more complicated than you want to do.

[GaryH]

Thanks for responding - I think Union could be used to simplify this flow somewhat, but my need is to identify and highlight items that are not duplicate entries before the removal of duplicates so I need to get this Update Item component working somehow.

[Matthy79]

Hello @GaryH ,

sorry for the confusion. Here is a sample code how this could be done:

{"id":"6e684ed2-27bf-4327-aa96-60b0279d69b3","brandColor":"#8C3900","connectionReferences":{"shared_sharepointonline":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-b959cb53-5ecb-4b41-805f-06e7795a0bd2"}},"shared_office365":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_office365/connections/1597557c9920416e9a930edfee990646"}},"shared_commondataserviceforapps":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_commondataserviceforapps/connections/62947705b1a746319ca1e59aae985afc"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Scope_mark_fix_version","operationDefinition":{"type":"Scope","actions":{"Compose_fix_version":{"type":"Compose","inputs":[{"FixVersion":"2301","Project":"A","Key":"A1"},{"FixVersion":"2301","Project":"A","Key":"A1"},{"FixVersion":"2301","Project":"B","Key":"B1"},{"FixVersion":"2301","Project":"B","Key":"B1"},{"FixVersion":"2301","Project":"B","Key":"B2"}],"runAfter":{}},"Compose_xml_fix_version":{"type":"Compose","inputs":"@xml(json(concat('{ \"root\": { \"data\": ', outputs('Compose_fix_version'), '}}')))","runAfter":{"Compose_fix_version":["Succeeded"]}},"Select_fix_version":{"type":"Select","inputs":{"from":"@union(outputs('Compose_fix_version'),outputs('Compose_fix_version'))","select":{"FixVersion":"@if(equals(length(xpath(outputs('Compose_xml_fix_version'), concat('//root/data[FixVersion=\"', item()['FixVersion'], '\" and Project=\"', item()['Project'], '\" and Key=\"', item()['Key'], '\"]'))), 1), 'CHECK', item()['FixVersion'])","Project":"@item()['Project']","Key":"@item()['Key']"}},"runAfter":{"Compose_xml_fix_version":["Succeeded"]}}},"runAfter":{}}}

[GaryH]

Thanks for coming back on this.

I think I have my head wrapped around this, and - correct me if I'm wrong - it looks like it would work really well if the fix version, project and key fields are clearly defined values. However, looking at this part,

Compose_fix_version":{"type":"Compose","inputs":[{"FixVersion":"2301","Project":"A","Key":"A1"},{"FixVersion":"2301","Project":"A","Key":"A1"},{"FixVersion":"2301","Project":"B","Key":"B1"},{"FixVersion":"2301","Project":"B","Key":"B1"},{"FixVersion":"2301","Project":"B","Key":"B2"

it will not really work unless everything is predefined in this manner?

My real dataset will have rolling fix versions, 20+ projects, and generated keys when a new story is created (numbers in the hundreds), which is why I'm trying to use the dynamic values from the SharePoint list we're keeping.

I've built a PowerApp for the team to handle the checking prompts, so we don't need to work from a large SharePoint list but streamlining this duplicate handling is the sticking point - I'm very new to Power Automate concepts, so very much appreciate your patience and help 🙂

[Matthy79]

Hello @GaryH 

Just try it, it works 🙂

[GaryH]

To the best of my ability, I recreated the flow from what I could see in your code and the notes from your screenshots:

but, alas:

[Matthy79]

You don't have to recreate anything. I already posted a sample flow. You just need to copy the JSON provided and paste it using the clipboard.

Press "Ctrl+v"

After that you can click on it and it will be added to your sample flow.

Sorry for the confusion. I thought this was already clear.

For your error. Your JSON is not valid. After the closing of the array there shouldn't be anything more. Also the xml is wrong because it has the escape slashes. Don't mind about it. Just copy the code provided and paste it into a dummy flow to see what it does.

[GaryH]

I can see from the outputs that your code returns "check" where expected

But I'm afraid I still have the same difficulty I started the thread with - writing the results to SharePoint 🙈

[Matthy79]

Hello @GaryH 

If you want to update an item using this array, it won't work. You will need the unique id (integer value) in this array too. Since union won't work for the whole array (because the id is unique) you will have to remove id first and add it again during the select statement using xpath again. That is why Power Automate creates a second apply to each, because you picked the ID from another array.

Maybe you should start with something simpler for the first project?

[GaryH]

Hi Matthy,

I'm afraid this is a need-driven project rather than an interest-driven one, so I need to solve this. It sounds like I was closer to what I needed with my original solution, but thank you for trying to help 🤝

[takolota]

@GaryH 

Based on this…

It looks like you want to remove any exact duplicates 1st (where the key matches, then find any records with the same/duplicate VersionProject values & mark those with a CHECK

I think for that you may have to use the set-up twice. So run a standard version of the template flow using only the Key column in the initial Select, then create a copy of the same set-up below that, only you will use VersionProject in the Select instead of the Key & you will use an Update action instead of a Delete action.

[GaryH]

Hi Takolota,

Sorry if I've been unclear in my original post but I'll try to rephrase what my challenge is.

My columns are:

Fix Version

Project

Key

I have been able to successfully delete duplicates using your package but I am trying to modify it so that I can

Detect unique "key" values, label them with "CHECK" under Fix Version, then delete duplicates.

I believe it occurred because I misunderstood how the Apply to Each step is working. I thought if I wrote "CHECK" in the appropriate Update Item field then it would only apply to filtered unique keys but, as you can see, my out put is nothing but "CHECK" all the way down the line.

I then tried to make a condition to test if the output is a 1 or 0 and 

It also hit me with 

The expected output was that only A1 should have "CHECK" beside it.

For me, I'm not understanding why the fields I refer to is good enough to have the duplicates deleted, but when using the Update Item function, it all falls apart.

Any help or insight on what to read into would be welcome here.

Thanks,

Gary

[takolota]

@GaryH 
That doesn’t really make sense as all your Key values are unique.

If you mean you want to get distinct rows for Project & Fix version columns, then I have this similar set up for getting distinct records based on set columns…

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Get-Distinct-Records/td-p/2191533

[GaryH]

Hi Takolota,

Yes, that screenshot is the output. The list was populated with

A1, A2, B1, B2

then

A2, B1, B2, A3

were added

meaning my expected output would have been deleted duplicates (which worked) and as A1 was missing from the second set imported to the list (meaning A1 is a unique value) only  A1 and A3 should have the Fix Version as "Check".

A couple of weeks ago I was able to adjust your 'delete duplicate' template to return unique values instead of duplicates (the screenshots in the first post), but I'm having real trouble getting Update Items to target only those rows.

Ideally, I would be able to differentiate between A1 (was on the original list) and A3 (completely new) but after the journey this has been, I'll just be happy to ditch that idea. 😂