topic Re: Remove duplicates from several varibles in DAX Commands and Tips

Remove duplicates from several varibles

MichelleRA_24 — Thu, 29 Aug 2024 17:36:32 GMT

Hi team!

I hope you are doing great! I thanks for your support.

I need your help to figure out how to create a logic that results in the "Result" column, considering the number_of_people,

sequence_number,

payment_date,

modified_date,

creation_date,

Filter Address=1,

status=ERROR_TIMBRE.

If the sequence number is the same for an employee, keep a line that meets the Address Filter = 1 and status = ERROR_TIMBRE, and use the most recent date from any of the 3 date columns. If the employee has different sequence numbers and some are repeated, do the same, making sure to keep the line with the most recent date compared to the others

number_of_people	sequence_number	modified_date	payment_date	creation_date	Filter Address	status	Result
32000127	1	12/28/2023	6/3/2024	6/3/2024	0	ERROR_TIMBRE	Remove
32000127	1	1/4/2024	6/3/2024	6/3/2024	1	ERROR_TIMBRE	Remove
32000127	1	5/31/2024	6/3/2024	6/3/2024	1	ERROR_TIMBRE	Keep
32000185	11	12/26/2023	5/15/2024	6/7/2024	0	ERROR_TIMBRE	Remove
32000185	11	12/26/2023	5/15/2024	6/12/2024	0	ERROR_TIMBRE	Remove
32000185	11	12/26/2023	5/15/2024	6/13/2024	0	Emitido	Remove
32000185	11	6/12/2024	5/15/2024	6/7/2024	1	ERROR_TIMBRE	Remove
32000185	11	6/12/2024	5/15/2024	6/12/2024	1	ERROR_TIMBRE	Keep
32000185	11	6/12/2024	5/15/2024	6/13/2024	1	Emitido	Remove
32000185	12	12/26/2023	5/30/2024	6/7/2024	0	ERROR_TIMBRE	Remove
32000185	12	12/26/2023	5/30/2024	6/12/2024	0	ERROR_TIMBRE	Remove
32000185	12	12/26/2023	5/30/2024	6/13/2024	0	Emitido	Remove
32000185	12	6/12/2024	5/30/2024	6/7/2024	1	ERROR_TIMBRE	Remove
32000185	12	6/12/2024	5/30/2024	6/12/2024	1	ERROR_TIMBRE	Keep
32000185	12	6/12/2024	5/30/2024	6/13/2024	1	Emitido	Remove
32000185	13	12/26/2023	6/14/2024	6/13/2024	0	Emitido	Remove
32000185	13	6/12/2024	6/14/2024	6/13/2024	1	Cancelado	Remove
32000185	14	12/26/2023	6/28/2024	6/27/2024	0	Emitido	Remove
32000185	14	6/12/2024	6/28/2024	6/27/2024	1	Emitido	Remove


Table name: Secuencias


Expected results
number_of_people	sequence_number	modified_date	payment_date	creation_date	Filter Address	status	Result
32000127	1	5/31/2024	6/3/2024	6/3/2024	1	ERROR_TIMBRE	Keep
32000185	11	6/12/2024	5/15/2024	6/12/2024	1	ERROR_TIMBRE	Keep
32000185	12	6/12/2024	5/30/2024	6/12/2024	1	ERROR_TIMBRE	Keep

Re: Remove duplicates from several varibles

lbendlin — Fri, 30 Aug 2024 00:54:40 GMT

You posted this in the DAX section. Is it ok to do this via Power Query instead?

making sure to keep the line with the most recent date compared to the others

That is a very ambiguous requirement. Your sample data only has two rows per group. Please provide better sample data.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZTLCoMwEEX/xbWQZJKo6xYXpZRC6E6kSJuFUGsXpd/fBBEf0TxsF4GJymHuPWBRRBQwxgTSKI6IPoAgQ4CBqkuCqB7ZdMTq5EKcxfVyOO1Erq5CNu1HRmVs8hBbQIxGEkLjiJJg3FHK1wiWcf1ZnzXps3JE+MBLQ8J6EfWrfyOnSpr6Xd9bO228x0piHyE+wOF5iBMLb+rYlRbM7ij+SbCDuEWwCxkmGIz+FhP7C7YDwwU7eGGC6bw7RWCbu6Oz1dZgerV99bzJR2XDMXO57rfa8SANWm7e9xpsubfyCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [number_of_people = _t, sequence_number = _t, modified_date = _t, payment_date = _t, creation_date = _t, #"Filter Address" = _t, status = _t, Result = _t]), #"Filtered Rows" = Table.SelectRows(Source, each ([status] = "ERROR_TIMBRE") and ([Filter Address] = "1")), #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"modified_date", type date}, {"payment_date", type date}, {"creation_date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Max Date", (k)=> let f = Table.SelectRows(#"Changed Type", each [number_of_people]=k[number_of_people] and [sequence_number]=k[sequence_number]) in List.Max(List.Combine({f[modified_date],f[payment_date],f[creation_date]}))) in #"Added Custom"

Re: Remove duplicates from several varibles

MichelleRA_24 — Fri, 30 Aug 2024 16:45:53 GMT

Hi Ibendlin, thanks for helping me, I don´t have problem using Power query

This is all I have; there are more than 100 employees, but they have the same data.

In this case, the only difference is that one column may be missing dates, which can happen.

So, the logic is the same: each employee must have a unique sequence and be the most recent among the three dates. The rows should also maintain Filter Address=1 and Status=ERROR_TIMBRE. For the rows that meet these criteria, indicate 'Keep', and for those that do not, indicate 'Remove'.

number_of_people

sequence_number

modified_date

payment_date

creation_date

Filter Address

status

Result

32000120

12/26/2023

6/14/2024

6/13/2024

ERROR_TIMBRE

32000120

1/4/2024

6/14/2024

6/13/2024

ERROR_TIMBRE

Keep

32000120

12/26/2023

6/28/2024

6/27/2024

ERROR_TIMBRE

32000120

1/4/2024

6/28/2024

6/27/2024

ERROR_TIMBRE

Keep

32001297

2/22/2024

6/7/2024

ERROR_TIMBRE

32001297

2/22/2024

6/12/2024

ERROR_TIMBRE

32001297

2/25/2024

6/7/2024

ERROR_TIMBRE

32001297

2/25/2024

6/12/2024

ERROR_TIMBRE

Keep

32001297

2/22/2024

6/14/2024

Emitido

32001297

2/25/2024

6/14/2024

ERROR_TIMBRE

Keep

32001297

2/22/2024

6/21/2024

ERROR_TIMBRE

32001297

2/25/2024

6/21/2024

ERROR_TIMBRE

Keep

32001297

2/22/2024

6/28/2024

Emitido

32001297

2/25/2024

6/28/2024

ERROR_TIMBRE

Keep

Expected results

number_of_people

sequence_number

modified_date

payment_date

creation_date

Filter Address

status

Result

32000120

1/4/2024

6/14/2024

6/13/2024

ERROR_TIMBRE

Keep

32000120

1/4/2024

6/28/2024

6/27/2024

ERROR_TIMBRE

Keep

32001297

2/25/2024

6/12/2024

ERROR_TIMBRE

Keep

32001297

2/25/2024

6/14/2024

ERROR_TIMBRE

Keep

32001297

2/25/2024

6/21/2024

ERROR_TIMBRE

Keep

32001297

2/25/2024

6/28/2024

ERROR_TIMBRE

Keep

Re: Remove duplicates from several varibles

MichelleRA_24 — Fri, 30 Aug 2024 16:47:47 GMT

number_of_people	sequence_number	modified_date	payment_date	creation_date	Filter Address	status	Result
32000120	15	12/26/2023	6/14/2024	6/13/2024	0	ERROR_TIMBRE
32000120	15	1/4/2024	6/14/2024	6/13/2024	1	ERROR_TIMBRE	Keep
32000120	16	12/26/2023	6/28/2024	6/27/2024	1	ERROR_TIMBRE
32000120	16	1/4/2024	6/28/2024	6/27/2024	1	ERROR_TIMBRE	Keep
32001297	23	2/22/2024		6/7/2024	0	ERROR_TIMBRE
32001297	23	2/22/2024		6/12/2024	1	ERROR_TIMBRE
32001297	23	2/25/2024		6/7/2024	1	ERROR_TIMBRE
32001297	23	2/25/2024		6/12/2024	1	ERROR_TIMBRE	Keep
32001297	29	2/22/2024	6/14/2024	6/14/2024	0	Emitido
32001297	29	2/25/2024	6/14/2024	6/14/2024	1	ERROR_TIMBRE	Keep
32001297	30	2/22/2024	6/21/2024	6/21/2024	1	ERROR_TIMBRE
32001297	30	2/25/2024	6/21/2024	6/21/2024	1	ERROR_TIMBRE	Keep
32001297	31	2/22/2024	6/28/2024	6/28/2024	0	Emitido
32001297	31	2/25/2024	6/28/2024	6/28/2024	1	ERROR_TIMBRE	Keep

Expected results
number_of_people	sequence_number	modified_date	payment_date	creation_date	Filter Address	status	Result
32000120	15	1/4/2024	6/14/2024	6/13/2024	1	ERROR_TIMBRE	Keep
32000120	16	1/4/2024	6/28/2024	6/27/2024	1	ERROR_TIMBRE	Keep
32001297	23	2/25/2024		6/12/2024	1	ERROR_TIMBRE	Keep
32001297	29	2/25/2024	6/14/2024	6/14/2024	1	ERROR_TIMBRE	Keep
32001297	30	2/25/2024	6/21/2024	6/21/2024	1	ERROR_TIMBRE	Keep
32001297	31	2/25/2024	6/28/2024	6/28/2024	1	ERROR_TIMBRE	Keep

Re: Remove duplicates from several varibles

lbendlin — Fri, 30 Aug 2024 17:20:20 GMT

and be the most recent among the three dates

That's the ambiguous statement - I think what you really want is "the most recent date in one of the columns if the other column have matching dates". Or something like that - sequence number 23 seems to be even more complex.

Re: Remove duplicates from several varibles

Anonymous — Mon, 02 Sep 2024 08:24:08 GMT

Thanks for the reply from lbendlin , please allow me to provide another insight:
Hi MichelleRA_24 ,

Here are the steps you can follow：

1. Create calculated column.

Result_test = var _modifieddate= MAXX( FILTER(ALL('Test'), 'Test'[number_of_people]=EARLIER('Test'[number_of_people])&&'Test'[Filter Address]=1&&'Test'[status]="ERROR_TIMBRE"&& 'Test'[modified_date]<=TODAY()),[modified_date]) var _count= COUNTX( FILTER(ALL('Test'),[number_of_people]=EARLIER('Test'[number_of_people])&&'Test'[sequence_number]=EARLIER('Test'[sequence_number])&&[modified_date]=_modifieddate),[number_of_people]) var _creationdate= MAXX( FILTER(ALL('Test'), 'Test'[number_of_people]=EARLIER('Test'[number_of_people])&&'Test'[Filter Address]=1&&'Test'[status]="ERROR_TIMBRE"&&'Test'[sequence_number]=EARLIER('Test'[sequence_number])&& 'Test'[creation_date]<=TODAY()),[creation_date]) return IF( _count=1&&'Test'[modified_date]=_modifieddate,"Keep", IF( _count>1&&'Test'[modified_date]=_modifieddate&&'Test'[creation_date]=_creationdate,"Keep",BLANK()))

2. Create calculated table.

Table = FILTER( 'Test',[Result_test]="Keep")

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly