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.
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
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 | |
100 | |
85 | |
77 | |
66 |
User | Count |
---|---|
120 | |
111 | |
95 | |
83 | |
75 |