cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
haliddelkic
Frequent Visitor

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

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. 

 

PBI Example.png

1 ACCEPTED SOLUTION

Hi  @haliddelkic ,

 

Sorry for misunderstanding.

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

vkellymsft_0-1634891149362.pngvkellymsft_1-1634891210999.png

After grouping,expanding another 2 columns:

vkellymsft_2-1634891243966.png

 

And you will see:

vkellymsft_3-1634891254728.png

Now you could remove all the "true" rows.

 

Best Regards,
Kelly

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

View solution in original post

3 REPLIES 3
v-kelly-msft
Community Support
Community Support

Hi  @haliddelkic ,

 

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

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

vkellymsft_0-1634885772386.png

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:

vkellymsft_1-1634886090094.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

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

Hi  @haliddelkic ,

 

Sorry for misunderstanding.

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

vkellymsft_0-1634891149362.pngvkellymsft_1-1634891210999.png

After grouping,expanding another 2 columns:

vkellymsft_2-1634891243966.png

 

And you will see:

vkellymsft_3-1634891254728.png

Now you could remove all the "true" rows.

 

Best Regards,
Kelly

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

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.