Forum Discussion
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"
| Date | Query | Domian |
| Wednesday, January 1, 2020 | Search Term 5 | domain2 |
| Wednesday, January 1, 2020 | Search Term 12 | domain0 |
| Wednesday, January 1, 2020 | Search Term 1 | domain1 |
| Thursday, January 2, 2020 | Search Term 1 | domain1 |
| Thursday, January 2, 2020 | Search Term 2 | domain1 |
| Thursday, January 2, 2020 | Search Term 1 | domain1 |
| Thursday, January 2, 2020 | Search Term 5 | domain2 |
| Thursday, January 2, 2020 | Search Term 13 | domain2 |
| Thursday, January 2, 2020 | Search Term 5 | domain2 |
| Thursday, January 2, 2020 | Search Term 2 | domain0 |
| Thursday, January 2, 2020 | Search Term 4 | domain0 |
| Thursday, January 2, 2020 | Search Term 3 | domain0 |
| Friday, January 3, 2020 | Search Term 1 | domain0 |
| Friday, January 3, 2020 | Search Term 3 | domain0 |
| Friday, January 3, 2020 | Search Term 1 | domain1 |
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
- mahoneypatMicrosoft 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- AnonymousNot applicable
Thank you mahoneypat ! Very effecitve way!