Remove dublicate Data with Power Automate

[TheGreatDensby]

Hey Fellows 🙂

I have a problem building a Flow. Maybe some of you can give me an inspiration.

So what i have build is a Custom Connector, importing Data from a hybrid Business Central into a custom Dataverse Table.

The Problem is, that i have to run the flow every 24 hours, so it get "new" Data from BC, but when i do that, it importing all the data again, without checking dublicates. Now i want something, detecting the dublicates and removing it.

My first thought was, i make a automated flow, with a automated trigger in dataverse "if a new item is created" it will look through the other items and if its a duplicate, it will delete it.
But no idea how i start there. Can somebody give me a hint? Or maybe there is another better way to check for duplicates?

Cheers

[Mira_Ghaly]

@TheGreatDensby

So your custom connector gets data and insert in dataverse? Can you share how it currently structured?

According to how it is currently structured you may want to do a list rows to check if the row already exists in dataverse prior to create a new record or your idea still works.

In either way you need list rows with OData filter and then a condition to check on the length of the List Rows and create new item if length of List Rows is 0.

[TheGreatDensby]

@Mira_Ghaly 
Hey Mira, thanks for your fast reply 🙂

So your custom connector gets data and insert in dataverse? Can you share how it currently structured?

-> I just build an automated flow, that called the custom connector, extraced the body and for every line, it created a new Dataverse Item with the dynamic properties. Thats working fine.

What i than did, i build another flow and everytime a new line in the dataverse table is added it list all lines in that table. After that, he do a check if the added row no. is equal to each of the row numbers in the table.
IF YES than he try to delete the line with the ODATA ID. But thats not really working . He dont get the right row ID to delete the line. 

[Mira_Ghaly · Accepted solution]

@TheGreatDensby

• So on your second flow:

First step should be List rows with Row Filter say your column where you want to verify duplicate against called AccountNumber, in other words account number is the column where you link BC and Dataverser

Note in the left side of your Row Filter you  should be using column schema name(name) and not the display name:

The above step List Rows its intention to check if this row already exists in DataVerse or No and by a check on the length of the List Rows output you can decide delete existing rows:

Expression at 1:

length(outputs('List_rows')?['body/value'])

[TheGreatDensby]

Thank you very much @Mira_Ghaly !

Its working now and its running fast.

[Berwynn]

Hey, 

I know I am a bit late to this, but though I'd leave a comment in case someone else comes across this thread.

Originally in my flow I had a very similar logic to this, however record stopped coming in a week ago (not sure if MS has changed something in the background). 
Regardless, what we need to do now is to check if there is more than 1 record and not more than 0.

Here is my logic behind it:

1. The record comes in and gets created.

2. We fetch the table with a given criteria.

3. The criteria will always return at least one record (the newly created one)

4. The newly created record is flagged as a duplicate and therefore is deleted.

So we can keep the existing logic in, it's just the case of changing the 0 (or in my case null) to a 1, ensuring the original record will exist at least once.

I hope this is helpful!