Forum Discussion
Compare one value to multiple columns values
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
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"I guess the disconnect is that I already have a table of thousands of lines that is updated daily (through a DataFlow). So, I need to use that table to create the filter. I attempted your solution and got an error. Any idea what went wrong?
Hi LFrench ,
Thanks for the clarification and thanks OwenAuger for your valuable reply.
From your error shown ("Token Literal expected"), it looks like the issue is with the final in statement. You're currently referencing:
in #"Expanded Disaster Responder GAPs (Totality)1"However, the actual final step in your query is named #"Added Match", so your in clause should reference that instead. Also, ensure all parts of the syntax are properly closed and matched.
Here's the corrected version of your M code using the dynamic comparison approach across all columns except the reference column (A):
let Source = Table.NestedJoin(#"DRO Work Assignments", {"Incident Account ID"}, #"Disaster Responder GAPs (Totality)", {"Account ID"}, "Disaster Responder GAPs (Totality)", JoinKind.LeftOuter), #"Expanded Disaster Responder GAPs (Totality)1" = Table.ExpandTableColumn(Source, "Disaster Responder GAPs (Totality)", {"GAPs.1", "GAPs.2", "GAPs.3", "GAPs.4", "GAPs.5"}), ReferenceColumn = "A", #"Added Match" = Table.AddColumn( #"Expanded Disaster Responder GAPs (Totality)1", "MATCH", each if List.Contains( Record.FieldValues(Record.RemoveFields(_, {ReferenceColumn})), Record.Field(_, ReferenceColumn) ) then "MATCH" else "MISMATCH", type text ) in #"Added Match"Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.Thank you.
