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
- AlexisOlson
Super User
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.
