Remove Duplicates with date condition

[Anonymous]

Hey all,

I have a table that looks as follows:

I want to remove the duplcate ID's but only based on the condition that it removes the old records of the survey. For example, ID 123 has been recorded twice - 7/15/2019 and 8/15/2019. I want to remove the record 7/15/2019 and only retain the latest survey details. My output should look as follows:

https://community.powerbi.com/t5/Desktop/Remove-duplicate-with-date-condition/m-p/702377#M339051

I followed the above thread for guidance, but I get an error:

What is the best way to do this? Thanks!

[Anonymous · Accepted solution]

Hi @Anonymous ,

You just need to change your edit query somethign like this:

1. Add comma

2. And than add the code you refered in the post.

3. then remove duplicatas on the ID . You will get the results you are looking for.

 My output:

Thanks,

Tejaswi

[Anonymous]

@Anonymous Thank u!

[Anonymous]

You can try this in Power Query:

1). Sort your data by ID (ascending or descending, doesnt matter)

2) Sort your Date Completed Descending (that does matter!)

3) GroupBy ID and Under "Operation" select All Rows

4) Need to add "GroupKind.Local" at the end of the formula that was automatically generated when you did #3

original:

= Table.Group(#"Sorted Rows", {"ID"}, {{"allrows", each _, type table [ID=number, Name=text, Date Completed=date, Q1=text, Q2=text]}})

modified:

= Table.Group(#"Sorted Rows", {"ID"}, {{"AllRows", each _, type table [ID=number, Name=text, Date Completed=date, Q1=text, Q2=text]}},GroupKind.Local)

5) Add a custom column with:

Table.FirstN([AllRows],1)

6) Remove all the other columns except for what was created in #5

7) Expand that column and Set data types

Final Table:

Full M Code (many of these steps could be combined, but that makes it harder to follow)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Date Completed", type date}, {"Q1", type text}, {"Q2", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{ {"ID", Order.Ascending},{"Date Completed", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"AllRows", each _, type table [ID=number, Name=text, Date Completed=date, Q1=text, Q2=text]}},GroupKind.Local),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.FirstN([AllRows],1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"ID", "Name", "Date Completed", "Q1", "Q2"}, {"ID", "Name", "Date Completed", "Q1", "Q2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"ID", Int64.Type}, {"Name", type text}, {"Date Completed", type date}, {"Q1", type text}, {"Q2", type text}})
in
    #"Changed Type1"

-Nick

[Anonymous]

@Anonymous Thank you!