Compare one value to multiple columns values

[LFrench]

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

[v-veshwara-msft]

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.

[v-veshwara-msft]

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.

[v-veshwara-msft]

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.

[OwenAuger]

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?

[LFrench]

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.

[OwenAuger · Accepted solution]

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"

[LFrench]

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?

[v-veshwara-msft]

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.