Forum Discussion

warnera's avatar
warnera
Helper I
6 years ago
Solved

Finding unique text in 2 different columns

Hello all, I am trying to create a column that displays only the unique text shared between two columns. Created sample example below. Will be working with large dataset. 

 

Column A                    Column B                    Desired Column

Jon, Joe, Julie           Joe, Julie, Steve                  Joe, Julie

Amy, Jamie               Jamie, Mike, Dave                 Jamie

 

Appreciate any help or insight

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    HI  warnera 

    You just miss "{ }' in this formula

    just adjust it as below:

    Text.Combine(List.Intersect({Text.Split([Column A],",") ,Text.Split([Column B],",")}),",")

    Result:

     

    Regards,

    Lin

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    warnera , Try in M power Query a new column

     

    Text.Combine(List.Intersect(Text.Split([Column A],",") ,Text.Split([Column B],",")),",")

    • warnera's avatar
      warnera
      Helper I
      6 years ago

      I am getting an error when trying this. Possibly because I oversimplified my example? The actual data from column A and B are as follows:

       

      Column A: Joe, Sam, Steve

      Column B: [users- 2020,05,20] Total. Steve, Sam, Jon, Mike

       

      Did I execute this wrong or does that change your response?

       

      Thanks again!

      • warnera's avatar
        warnera
        Helper I
        6 years ago

        I was also unable to replicate this on a test example.