Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
P8MCP
Frequent Visitor

Remove rows if value is found in another column

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:

P8MCP_0-1663250973773.png

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:
P8MCP_1-1663251032672.png

 

Many Thanks in advance.

1 ACCEPTED SOLUTION
rsbin
Super User
Super User

@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,

View solution in original post

3 REPLIES 3
rsbin
Super User
Super User

@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
Frequent Visitor

@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,

@P8MCP ,

Glad I was able to help and appreciate the feedback.

Best Regards,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.