Remove multiple rows with matching ID, based on a variable value in another column

[haliddelkic]

Power BI newbie here looking for some help. 

In my scenario, I have a dataset where the same customer may appear in multiple rows.

In each row, the customer's reference ID is the same but across other columns, the data values may vary. 

I am trying to find a way to run a partial text value match within one of those other columns (e.g. "Variable Value Column" in the dummy table below). In cases where there is a match with at least one row of the same individual, I wish to locate and remove ALL rows relating to that customer based on the Customer Reference Number. 

Using the dummy table below, I would like to run a partial text match against the "Variable Value Column" (e.g. using wildcard search text of "lue 3"), which will identify the row with the cell highlighted in red. I then wish to use the reference number column to identify all other matching rows for that individual and completely remove them from my dataset. 

Still learning the PowerBI terminology, so any (simplified) insight and direction would be most appreciated. 

[v-kelly-msft]

Hi  @haliddelkic ,

Go to query editor ,then create a  custom column as below:

=Text.Contains([Variable Value Column],"lue 3")

Then create a new table to remove the rows where the result is true:(Or you could filter out the rows where the result is true in the oringinal table)

= Table.SelectRows(#"Table",each [Custom]=false)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[haliddelkic]

Thanks for your help, Kelly.

However, I was looking to go a step further and remove all rows where the unique reference number is the same as the row where result is TRUE. 

So in this scenario, we've identified a single row and tagged it as TRUE using a custom column. We know that person is Fred Jones and his unique reference number is 1234.

Now I want to locate and remove all rows where reference # is 1234.

Hopefully, that makes more sense.
Halid

[v-kelly-msft · Accepted solution]

Hi  @haliddelkic ,

Sorry for misunderstanding.

You could group by reference number and make the setting as below:

After grouping,expanding another 2 columns:

And you will see:

Now you could remove all the "true" rows.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!