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?
