Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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())
@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())
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |