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 Date End Date Candidate No Candidate Name 1-Mar-2022 8-Feb-2023 100142992 Ali 26-Oct-2021 28-Feb-20...
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())
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())