How to filter duplicates out of a Dataverse table based on date

(0) Share

Report

Posted on by Aurora

364

Hi All,

I have an app where I am trying to create a bill of material for parts that my company needs to rework. I have a table in Dataverse called "AUNA Parts Product Structures" that has each "Parent Part" (PARPRT_02) in a column, and then each "Component Part" (COMPRT_02) in an additional column.

I have loaded the Dataverse table into excel to get a better view. In the image above, the 2 rows with a red dot need to be filtered out inside my app because they are older than the part with the same COMPRT_02. I also filter out any rows with a QTYPER_02 value of 0.

Currently this is all I have been able to come up with. I do not believe I can use the distinct function because I need to keep all the records associated with each part, so I can then lookup the parts that go into the parts that have been filtered out.

I hope this all makes sense.

if anyone has any suggestions or ideas where I could start with this, I would greatly appreciate it.

Categories:

Connecting to Data

General Questions

All responses (2)

Answers (0)

Aurora 364 on at

Like (0)

Report

Re: How to filter duplicates out of a Dataverse table based on date

@MissingLink I can try the second option and attempt to group by the max effective date. The data in this table comes in daily from our ERP system, so I am unable to add a column for each part that indicates if it has been superseded or not.

Thanks for the help
MissingLink 236 on at

Like (0)

Report

Re: How to filter duplicates out of a Dataverse table based on date

Hi @Aurora
I would consider one of 2 things for this problem.
Firstly - Are you able to add a column that indicates when a part has been superceeded? A simply T/F should work.
Then when a newer part is added, update the old part to 'True' - Now in your gallery, show only parts where superceeded='False'

The second option is to group your source data and include a max(EFFDTE02) in the grouping.

Also - if you are using dates to filter, this article recomends that you add another field where the date is stored as text in 'yyyymmdd' format as dates are not delegable in some scenarios
Solved: How to filter the data by Dates - Power Platform Community (microsoft.com)