Keep Duplicates with Conditions in Query Editor

[sya]

Hi All,

I'm new to Power BI and need help with the following problem.

Start Date	End Date	Candidate No	Candidate Name
1-Mar-2022	8-Feb-2023	100142992	Ali
26-Oct-2021	28-Feb-2022	100142992	Ali
1-Jan-2021	31-Dec-2022	100058909	Bob
1-Jan-2021	31-Dec-2022	100058909	Bob
1-Nov-2021	31-Dec-2021	100053113	Cindy
1-Jan-2022	28-Feb-2022	100053113	Cindy

I need to keep duplicate Candidate No. that meets the following conditions:

1. If first Start Date/End Date equal to second Start Date/End Date, only keep one

2. If first Start Date/End Date does not equal to second Start Date/End Date, keep if End Date is in the future (compared to today's date)

The result should be as follows:

Start Date	End Date	Candidate No	Candidate Full Name
1-Mar-2022	8-Feb-2023	100142992	Ali
26-Oct-2021	28-Feb-2022	100142992	Ali
1-Jan-2021	31-Dec-2022	100058909	Bob
1-Jan-2022	28-Feb-2022	100053113	Cindy

Please help 

Regards,

Sya

[amitchandak · Accepted solution]

@sya , Power Query code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtT1TSzSNTIwMlLSUbLQdUtNAnGMgRxDAwNDEyNLS5CEY06mUqxOtJKRma5/cglIhSFQ1Aiu3giHekNdr8Q8mHJjQ12X1GQk5QamFpYGlkC2U34Secr98sswlRvClBsbGoI84pyZl1KJar4RNteja4gFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, #"Candidate No" = _t, #"Candidate Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Candidate No", Int64.Type}, {"Candidate Name", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Duplicates", each [End Date] > DateTime.Date(DateTime.LocalNow()))
in
    #"Filtered Rows1"

Option 2:

DAX code on the base table you share. Not on power query 

Table = FILTER( distinct(Data) , [End Date] >= today())