Removing dupes in a column based on another column's value

[m_roussakis]

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

[m_roussakis]

I can't do that as there are some nulls in Column A I need to keep (these are not dupes).

[VahidDM]

Can you post sample data as text and expected output?

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

[m_roussakis]

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

[VahidDM · Accepted 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!! 

LinkedIn | Twitter | Blog | YouTube 

[VahidDM]

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