Forum Discussion
Compare one value to multiple columns values
Here's the challenge I am trying to solve for. I tried a few IF/THEN Statements and couldn't quite get there. So, hopefully you can offer some assistance.
I have Column A with a list of Assignment Codes, and then Columns B - F with Additional Assigment Codes. I am looking to make a column that compares Column A with all of the other columns and is results in "MATCH" is the text in Column A appears in any of the other columns or "MISMATCH" if it does not.
If it's easier, I also already have a single column that combines all of the B - F columns, if I could search Column A text within the text of another column.
| A | B | C | D | E | F | MATCH? |
| MC/001 | MC/001 | MC/002 | MC/003 | MC/004 | MC/005 | YES |
MC/002 | BD/001 | BD/002 | BD/003 | NO | ||
| MC/003 | BD/001 | BD/002 | BD/003 | MC/001 | MC/003 | YES |
| MC/004 | BD/001 | BD/002 | BD/003 | MC/001 | MC/002 | NO |
Apologies, could you clarify the requirement as I may have misunderstood something. 😅
I'm not sure what you mean by "compares Column A to Columns B, C, D ... and not the specific text within those columns."
My original understanding of the requirement was:
Within each row, if the value in column A is equal to at least one of the values in columns B, C, D, E, F, then add a column containing "MATCH" otherwise add a column containing "MISMATCH".
EDIT: Did you mean that, aside from column A, there are an arbitrary number of additional columns which you want to reference without specifying their names? If so, I would still use List.Contains along with Record functions as follows:
let Source = #table( type table [A = text, B = text, C = text, D = text, E = text, F = text], { {"MC/001", "MC/001", "MC/002", "MC/003", "MC/004", "MC/005"}, {"MC/002", "BD/001", "BD/002", "BD/003", null, null}, {"MC/003", "BD/001", "BD/002", "BD/003", "MC/001", "MC/003"}, {"MC/004", "BD/001", "BD/002", "BD/003", "MC/001", "MC/002"} } ), ReferenceColumn = "A", #"Added Match" = Table.AddColumn( Source, "MATCH", each if List.Contains(Record.FieldValues(Record.RemoveFields(_, {ReferenceColumn})), Record.Field(_, ReferenceColumn)) then "MATCH" else "MISMATCH", type text ) in #"Added Match"
8 Replies
Hi LFrench
With your table as shown above with columns A-F, you could use List.Contains as follows:
let Source = #table( type table [A = text, B = text, C = text, D = text, E = text, F = text], { {"MC/001", "MC/001", "MC/002", "MC/003", "MC/004", "MC/005"}, {"MC/002", "BD/001", "BD/002", "BD/003", null, null}, {"MC/003", "BD/001", "BD/002", "BD/003", "MC/001", "MC/003"}, {"MC/004", "BD/001", "BD/002", "BD/003", "MC/001", "MC/002"} } ), #"Added Match" = Table.AddColumn( Source, "MATCH", each if List.Contains({[B], [C], [D], [E], [F]}, [A]) then "MATCH" else "MISMATCH", type text ) in #"Added Match"Does this work as expected in your actual query?
I hadn't considered list.contain, but each column includes thousands of entries that change with each refresh, so I need a method that compares Column A to Columns B, C, D ... and not the specific text within those columns.
Apologies, could you clarify the requirement as I may have misunderstood something. 😅
I'm not sure what you mean by "compares Column A to Columns B, C, D ... and not the specific text within those columns."
My original understanding of the requirement was:
Within each row, if the value in column A is equal to at least one of the values in columns B, C, D, E, F, then add a column containing "MATCH" otherwise add a column containing "MISMATCH".
EDIT: Did you mean that, aside from column A, there are an arbitrary number of additional columns which you want to reference without specifying their names? If so, I would still use List.Contains along with Record functions as follows:
let Source = #table( type table [A = text, B = text, C = text, D = text, E = text, F = text], { {"MC/001", "MC/001", "MC/002", "MC/003", "MC/004", "MC/005"}, {"MC/002", "BD/001", "BD/002", "BD/003", null, null}, {"MC/003", "BD/001", "BD/002", "BD/003", "MC/001", "MC/003"}, {"MC/004", "BD/001", "BD/002", "BD/003", "MC/001", "MC/002"} } ), ReferenceColumn = "A", #"Added Match" = Table.AddColumn( Source, "MATCH", each if List.Contains(Record.FieldValues(Record.RemoveFields(_, {ReferenceColumn})), Record.Field(_, ReferenceColumn)) then "MATCH" else "MISMATCH", type text ) in #"Added Match"
Hi LFrench ,
Just checking in to see if you query is resolved and if any responses were helpful. If so, kindly consider marking the helpful reply as 'Accepted Solution' to help others with similar queries.
Otherwise, feel free to reach out for further assistance.
Thank you.
Hi LFrench ,
Following up to see if your query has been resolved. If any of the responses helped, please consider marking the relevant reply as the 'Accepted Solution' to assist others with similar questions.
If you're still facing issues, feel free to reach out.
Thank you.
Hi LFrench ,
We’re following up once more regarding your query. If it has been resolved, please mark the helpful reply as the Accepted Solution to assist others facing similar challenges.
If you still need assistance, please let us know.
Thank you.
