Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

M in Power Query: If with AND, OR

Hello,

 

Would you please help to guide me on add column in Power Query:

If Query contains Term 1, or Term 2, AND Domian is domain0, then in added column, fill "Keywords"

If Query contains Term 3, or Term 5, AND Domian is domain1, then in added column, fill "Keywords"

Else, fill "not Keywords"

 

DateQueryDomian
Wednesday, January 1, 2020Search Term 5domain2
Wednesday, January 1, 2020Search Term 12domain0
Wednesday, January 1, 2020Search Term 1domain1
Thursday, January 2, 2020Search Term 1domain1
Thursday, January 2, 2020Search Term 2domain1
Thursday, January 2, 2020Search Term 1domain1
Thursday, January 2, 2020Search Term 5domain2
Thursday, January 2, 2020Search Term 13domain2
Thursday, January 2, 2020Search Term 5domain2
Thursday, January 2, 2020Search Term 2domain0
Thursday, January 2, 2020Search Term 4domain0
Thursday, January 2, 2020Search Term 3domain0
Friday, January 3, 2020Search Term 1domain0
Friday, January 3, 2020Search Term 3domain0
Friday, January 3, 2020Search Term 1domain1

 

 

I tried to write in PQ, but failed:

 

each if Text.StartsWith([Domian],"domain0") and 
    (Text.Contains([Query], "Term 1") or Text.Contains([Query], "Term 2") )
    then "Keywords" 
    else if 
    each if Text.StartsWith([Domian],"domain1") and 
    (Text.Contains([Query], "Term 3") or Text.Contains([Query], "Term 5") )
    then "Keywords" 
    else "not Keywords")

 

 

 

By the way, what's the difference between "Each if" and "if"? I see each time i use the "Add conditional column", the code comes out with "each if"

Thanks in advance.

H

 

  • You can add a custom column with an expression like this in the pop-up windown.

     

    let thisquery = [Query] in
    if (List.Count(List.Select({"Term 1", "Term 2"}, each Text.Contains(thisquery, _))) > 0 and [Domain] = "domain0") or (List.Count(List.Select({"Term 3", "Term 5"}, each Text.Contains(thisquery, _))) > 0 and [Domain] = "domain1") then "keywords" else "no keywords"

     

    Also, this article describes "each" well.

    The Each Keyword in Power Query - The Excelguru BlogThe Excelguru Blog


    Pat

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee

    You can add a custom column with an expression like this in the pop-up windown.

     

    let thisquery = [Query] in
    if (List.Count(List.Select({"Term 1", "Term 2"}, each Text.Contains(thisquery, _))) > 0 and [Domain] = "domain0") or (List.Count(List.Select({"Term 3", "Term 5"}, each Text.Contains(thisquery, _))) > 0 and [Domain] = "domain1") then "keywords" else "no keywords"

     

    Also, this article describes "each" well.

    The Each Keyword in Power Query - The Excelguru BlogThe Excelguru Blog


    Pat