Remove rows if value is found in another column

[P8MCP]

Hi Guys,

So I have a problem where I have data coming in from two different sources with different ID's. Often they are duplicates and I want to get rid of them. This is how the data is structured:

What I am trying to do is, whenever the value in "Workable_Code" column is found in the "Code" column, for the entire row to be removed so that I do not have duplicates.

This diagram may explain it easier:

Many Thanks in advance.

[rsbin · Accepted solution]

@P8MCP ,

I'm not sure how this could be done in Power Query, but I think I have a solution in DAX.

Create a Calculated Column:

Flag = SWITCH(
            TRUE(),
           [Workable_Code] = Blank(), 0,
           ISERROR(LOOKUPVALUE( Code[Workable_Code], [Code],[Code] )) = FALSE(), 1 )

Basically, I am flagging each record where it does find a match.

CodeWorkable_CodeFlag

IOG423		0
IOG476		0
IOG420		0
ISM-247	IOG476	1

Then you can set your page filter or report filter to Exclude records where Flag = 1.

If you really need to do this in Power Query, you can try searching for a way to apply the same logic.

Hope you might be able to make this work for you either way.

Regards,

[P8MCP]

@rsbin 

This is genius! It means that I won't actually be deleting my results from the data, but simply hiding them. That was what I was trying to achieve but didn't think it was possible.

This is outstanding I really appreciate your help 🙂 What a great community!

Regards,

[rsbin]

@P8MCP ,

Glad I was able to help and appreciate the feedback.

Best Regards,