Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have duplicate values in column B.
I only want to keep the values in column B if Column A is NOT null. Column A is a date field.
So how I did it was sort column A to Descending. Then I removed dupes in Column B.
Will this do the trick? My dataset is massive to it's hard for me to know if this worked or not.
Thanks
Solved! Go to Solution.
Try to add a new column with this DAX code, then use that to set the filter:
Keep/Remove =
VAR _Count =
CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Column2] ) )
RETURN
IF (
_Count = 1,
"Keep",
IF ( _Count = 2 && 'Table'[Column1] = BLANK (), "Discard", "Keep" )
)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
I can't do that as there are some nulls in Column A I need to keep (these are not dupes).
Can you post sample data as text and expected output?
Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/
Column A | Column B
null | apple <---dupe discard
Jan 1 | apple <---dupe keep
null | banana <--dupe discard
Jan 5 | banana <---dupe keep
null | orange <---not a dupe keep
etc etc etc
Try to add a new column with this DAX code, then use that to set the filter:
Keep/Remove =
VAR _Count =
CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Column2] ) )
RETURN
IF (
_Count = 1,
"Keep",
IF ( _Count = 2 && 'Table'[Column1] = BLANK (), "Discard", "Keep" )
)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Hi @m_roussakis
I think if you set a filter on Column A in Power Query to exclude all blank/null values that would solve your issue.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn | Twitter | Blog | YouTube
User | Count |
---|---|
93 | |
85 | |
76 | |
66 | |
62 |
User | Count |
---|---|
110 | |
96 | |
95 | |
64 | |
57 |