Forum Discussion

P8MCP's avatar
P8MCP
Frequent Visitor
3 years ago
Solved

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:

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.

Need Help
  • rsbin's avatar
    rsbin
    3 years ago

    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,

3 Replies

  • rsbin's avatar
    rsbin
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      P8MCP
      Frequent Visitor
      3 years ago

      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's avatar
        rsbin
        Icon for Community Champion rankCommunity Champion
        3 years ago

        P8MCP ,

        Glad I was able to help and appreciate the feedback.

        Best Regards,