Duplicate data in entities created using ODATA after recurring refresh

[krabpcs]

I am exporting data from Business Central to PowerApp using OData. After importing I am creating Dataflows to create Entities in PowerApps. When creating the entity, the last step is to setup refresh and I set it up to refresh every 10 hours. But I see for every 10 hours, a snapshot of the data getting loaded instead of pushing only the changed records (UPSERTS) into the Entity, causing it to have duplicate data.

How do I remove those duplicates?

[v-bofeng-msft]

Hi @krabpcs :

Could you tell me:

• Do you want to delete duplicate records in the table?
• Is there a field with a unique value in your table to distinguish different records?

My method is to find a duplicate set of records through the primary key, and then delete one of the duplicate records through removeif. Just traverse all the primary key values, you can delete all duplicate records in the table.

I've made a test for your reference:

My data source:'List A'

1\Create a canvas app.

2\Add a button and set it's OnSelect property to:

ForAll(
    Distinct(
        'List A',
        Title
    ),/*Get the list of titles after deduplication and traverse*/
    If(
        CountRows(
            Filter(
                'List A',
                Title = Result
            )
        ) > 1,/*Determine if there are duplicate values*/
        Remove(
            'List A',
            LookUp(
                'List A',
                Title = Result
            )/*If there are duplicate values, delete the first record found*/
        )
    )
)

Duplicate record has been deleted

In addition, if there are multiple duplicate values for a record, you can execute this code several times.

Best Regards,

Bof

[krabpcs]

Thanks a lot for replying back to me.

For your question

• Do you want to delete duplicate records in the table? Not at this point of time as I want the refreshes to not load the duplicate values.
• Is there a field with a unique value in your table to distinguish different records? Yes, I do have a Primary Key which is unique value field
  

Currently, I am at a stage where my work is still young in Development and what I want to do is

1. Prevent it from loading the whole snapshot of the data into the entity after every refresh instead of just the new/changed records.
2. Or if there is a automatic way of deleting the older snapshots within the entity once the latest snapshot is in as each refresh is loading new snapshot.

I

[v-bofeng-msft]

Hi @krabpcs :

Do you wish to update the entitiy with a new data sheet? Which duplicate records perform update or replace operations? Do new records perform new operations?

If so,please try canvas app.

1\add a button

OnSlelect:

Forall(yournewdatasource,
    If(Primarykey in yourentitiy.Primarykey,
       Patch(yourentitiy,
             lookup(yourentitiy,Primarykey=yournewdatasource[@yournewdatasource]),
             {column1:xx,column2:xxx}),
       Patch(yourentitiy,
             defaults(yourentitiy),
             {column1:xx,column2:xxx})
  )
)

Best Regards,

Bof