Forum Discussion

CHARLEA1's avatar
CHARLEA1
Frequent Visitor
4 years ago
Solved

Comparing two columns for same values using powerquery

Hi there, 

first time posting but I have been looking around a lot.

I'm trying to create a custom column which identifies for which values in column B exists in column A (i.e. they do not need to exist on the same row).
For example: 
column A   column B   custom column
orange       banana       FALSE
apple         orange        TRUE
mango       pear            FALSE

 

I have seen solutions in DAX although I need this in powerquery, because I intend to pivot the table depending on the results.

From this article: https://towardsdatascience.com/power-bi-functions-list-contains-list-containsany-list-containsall-87ec48bdd68a I tried to use the following formula: 
= Table.AddColumn(#"Previous Step", "Custom", each List.Contains({#"Previous Step"[column A]},[column B]))

But it has not worked (everything outputs as false).

Thanks in advance!

  • Thanks,

    solution ended up being: = Table.AddColumn(#"Previous Step", "Custom", each List.Contains(#"Previous Step"[column A], [column B]))

3 Replies

  • CHARLEA1's avatar
    CHARLEA1
    Frequent Visitor

    Thanks,

    solution ended up being: = Table.AddColumn(#"Previous Step", "Custom", each List.Contains(#"Previous Step"[column A], [column B]))

    • Anonymous's avatar
      Anonymous
      Not applicable

      Darn I didn't realize that I left your opening brace in there!

       

      --Nate

  • Anonymous's avatar
    Anonymous
    Not applicable

    It'll work if you rewrite it like:

     

    = Table.AddColumn(#"Previous Step", "Custom", each List.Contains({#"Previous Step[column A], [column B]))

     

    --Nate