How to transform & compare 2 columns having multiple values with delimiter

[abhi139]

Hello All, I need your help in comparing two columns having delimiter seperated values, with transformation required based on 2nd table. I need to compare Column 1 and Column 2, both columns have delimiter seperated text values and also transform column 2 based on Table 2 (for comparison). 

The sequence/placement of values doesnt matter - e.g. Red is in first place in row 1 but equivalent value in Column 2 (C1) is in second place.  

Table 1

Sr	Column1	Column2	Column3 (Result)
1	Red; Blue; Pink	C2; C1; C3	Match
2	Yellow; Pink	C4	Mismatch
3	Blue; Red	C1;C2	Match

Table 2

Column1	Column2
Red	C1
Blue	C2
Pink	C3
Yellow	C4

[AlexisOlson · Accepted solution]

Are you trying to do this in the query editor (using M) or in your model (using DAX)?

It's not super pretty, but you can do this with a custom column in the query editor. The code for that step would look something like this (assuming Column1 and Column2 have had spaces removed):

= Table.AddColumn(#"Previous Step", "Column3", (r) => if Table.SelectRows(Table2, each List.Contains(Text.Split(r[Column1], ";"), [Column1]))[Column2] = List.Sort(Text.Split(r[Column2], ";")) then "Match" else "Mismatch", type text)

This code checks if two lists are equal. The first list is Table2[Column2] where Table2 has been filtered to only include values from that specific row in Table1[Column1]. The second list is Table1[Column2] value in that row turned into a list by splitting on ";" and sorting alphabetically (to match the order in Table2).

[abhi139]

Thank you, the solution worked perfectly.