Finding unique text in 2 different columns

[warnera]

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

[amitchandak]

@warnera , Try in M power Query a new column

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

[warnera]

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]

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

[v-lili6-msft · Accepted solution]

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