Forum Discussion
How to transform & compare 2 columns having multiple values with delimiter
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 |
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).
2 Replies
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).
Thank you, the solution worked perfectly.
