Forum Discussion

electrobrit's avatar
electrobrit
Icon for Post Patron rankPost Patron
6 years ago
Solved

in Power Query: If 2 columns contain specific text then this, else, this

Power Query: what is formula? If Column A contains "arlene" and column B contains "arlene", then YES, otherwise, NO. Column A Column B Condition John Peters [email protected] NO ...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    6 years ago

    Hi electrobrit 

     

    As a custom column

     

    =Text.Contains([Column A],"arlene",Comparer.OrdinalIgnoreCase) 
and 
Text.Contains([Column B],"arlene",Comparer.OrdinalIgnoreCase)
  • ChrisMendoza's avatar
    ChrisMendoza
    6 years ago
    electrobrit wrote:

    The column results are "error"

    What was the error?

     

    electrobrit wrote:

    1. how do I add the Yes if true, no if not? I tried to add at end and it didn't work.

    = if Text.Contains([Column A],"arlene",Comparer.OrdinalIgnoreCase) 
    and 
    Text.Contains([Column B],"arlene",Comparer.OrdinalIgnoreCase) = true then "YES" else "NO"
    electrobrit wrote:

    2. it would not take the "and" so I used "&" and it seemed to work when doing custom column (it said not syntax error have been detected) but is that correct for what I'm trying to do?

    'and' worked for me, unsure why it did not work for you

     

Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
6 years ago

Hi electrobrit 

 

As a custom column

 

=Text.Contains([Column A],"arlene",Comparer.OrdinalIgnoreCase) 
and 
Text.Contains([Column B],"arlene",Comparer.OrdinalIgnoreCase)
  • electrobrit's avatar
    electrobrit
    Icon for Post Patron rankPost Patron
    6 years ago

    Zubair_Muhammad thank you for your response! 
    The column results are "error"
    1. how do I add the Yes if true, no if not? I tried to add at end and it didn't work.

    2. it would not take the "and" so I used "&" and it seemed to work when doing custom column (it said not syntax error have been detected) but is that correct for what I'm trying to do?

    • ChrisMendoza's avatar
      ChrisMendoza
      Icon for Resident Rockstar rankResident Rockstar
      6 years ago
      electrobrit wrote:

      The column results are "error"

      What was the error?

       

      electrobrit wrote:

      1. how do I add the Yes if true, no if not? I tried to add at end and it didn't work.

      = if Text.Contains([Column A],"arlene",Comparer.OrdinalIgnoreCase) 
      and 
      Text.Contains([Column B],"arlene",Comparer.OrdinalIgnoreCase) = true then "YES" else "NO"
      electrobrit wrote:

      2. it would not take the "and" so I used "&" and it seemed to work when doing custom column (it said not syntax error have been detected) but is that correct for what I'm trying to do?

      'and' worked for me, unsure why it did not work for you