Search code examples
excelpowerquerym

Count if or match in another column in same table

I have a Column A and Column B in a table:

Table

I want to create a calculate Column that says "Yes" if the letter in Column A was found in Column B, and "No" if that is not the case (in power query so in language M).

I have read a lot about Calculate functions, filtering the table, Search Functions etc (even merging the table with itself but my file is already very heavy and I am not sure the solution is adapted). But when I try all these solutions and try to adapt them to my case, I get either error or circular reference.

Solution

  • When you build queries in the query editor it is done in steps. In order to avoid the circular reference, you need to make sure that the step where you create your custom column references the previous step and not the query name.

    For example, if my query is named Table01 and I try to write this custom column formula

     = if List.Contains(Table01[Column B], [Column A]) then "Yes" else "No"

    then I will get a circular reference error since the query name refers to the result of the final step.

    However, if my previous step is named #"Changed Type", then I can write this instead

     = if List.Contains(#"Changed Type"[Column B], [Column A]) then "Yes" else "No"

    and it works just fine.

    Screenshot