Forum Discussion

sya's avatar
sya
Helper I
4 years ago
Solved

Keep Duplicates with Conditions in Query Editor

Hi All,

 

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

 

Start DateEnd DateCandidate NoCandidate Name
1-Mar-20228-Feb-2023100142992Ali
26-Oct-202128-Feb-2022100142992Ali
1-Jan-202131-Dec-2022100058909Bob
1-Jan-202131-Dec-2022100058909Bob
1-Nov-202131-Dec-2021100053113Cindy
1-Jan-202228-Feb-2022100053113Cindy

 

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 DateEnd DateCandidate NoCandidate Full Name
1-Mar-20228-Feb-2023100142992Ali
26-Oct-202128-Feb-2022100142992Ali
1-Jan-202131-Dec-2022100058909Bob
1-Jan-202228-Feb-2022100053113Cindy

 

Please help 

 

Regards,

Sya

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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())

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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())