Remove duplicates based off of max date value from another column

[Anonymous]

Hello,

I'd like to remove duplicates from the 'Key' column, keeping only the rows which are the max of the 'Date' column.  Suggestions?

Much obliged,

-Red

What I have:

col1	col2	col3	Key	Date
asdf	1111	1	2310-209-2	11/1/2019
gfdag	22	2	2310-209-2	12/1/2019
fdfd	1	3	2310-210-2	11/2/2019
fdfa	4	4	2310-210-2	12/25/2019

What I want:

col1	col2	col3	Key	Date
gfdag	22	2	2310-209-2	12/1/2019
fdfa	4	4	2310-210-2	12/25/2019

[Adamtall]

Hi,

You can do a Group by in PowerQuery.

Use Key and operation Max with your date column.

/Adam

[v-easonf-msft]

Hi , @Anonymous 

According to you description ,you can  follow these steps:

1.create calculate column in table:

Column = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Key]))

 

2.create  calculate table:

Table copy = SELECTCOLUMNS(FILTER('Table','Table'[Column]='Table'[Date]),"col1",'Table'[col1],"col2",'Table'[col2],"col3",'Table'[col3],"Key",'Table'[Key],"Date",'Table'[Date])

Here is a sample I made:

URL: https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/ETfpMdkq_uFGt63Ensazfj4BmRyzgtNDoy1n8nGl9jxi-A?e=BaMAnP

In  addition ,you can refer to related post:

URL: https://community.powerbi.com/t5/Desktop/Remove-duplicates-based-on-values/td-p/59148

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

You can have following for each column Col1 , col2, col3

Measure = 
VAR _Key = MAX ( 'Table'[Key] )
VAR _date = CALCULATE ( MAX( 'Table'[Date] ), ALLSELECTED ( 'Table' ),  'Table'[Key] = _Key ) 
RETURN CALCULATE ( MAX ( 'Table'[col3] ), VALUES ( 'Table'[Key] ), 'Table'[Key] = _Key, 'Table'[Date] = _date )

Another way to use addcolumns and summarize and use the same max filters there

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601