Power Query: If Only
12 August 2020
Welcome to our Power Query blog. This week, I look at using the ‘if’ statement.
The following M code is show in the Advanced Editor. The highlighted text shows a valid ‘if’ statement:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/193/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
The highlighted M code is:
if [Name] = "Mary" then "Yes" else "No"
This includes the three keywords in an ‘if’ statement; if, then and else.
This particular ‘if’ statement has been generated using a conditional column (available on the ‘Add Column’ tab), which is the easiest way to start using ‘if’ statements, since Power Query does all the work.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/193/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
However, there are limits when using a conditional column to write M code. I may only specify conditions that involve one column at a time, so I can’t for instance say that two conditions need to be true, or that one of two conditions need to be true. Furthermore, I can only specify that one column needs to have a particular value or match another column. I can’t use another M function such as #date – I would have to create a column to help me do this. Finally, the result is either a column or a value; nothing more complex is possible this way. If I want to create a column using more complex methods, then an alternative approach is to use a custom column from the ‘Add Column’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/193/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
I have added a column which has a value of ‘Yes’ if either of two values are true, and the Name value plus some text otherwise. I can view my code in the Advanced Editor:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/193/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
The M code for the ‘if’ statement is:
if [Name] = "Mary" or [Name] = "John" then "Yes" else [Name] & " not Mary"
I have been able to make a more complex if statement using the custom column.
I can also nest my ‘if’ statements to give more flexibility to my column:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/193/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
If I view this in the Advanced Editor, I can see the M code:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/193/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
The M code is:
if [Name] = "Mary" or [Name] = "John" then "Yes" else if [More Companies.Head Company]= "Tentuniverse" then "maybe" else [Name] & " not Mary"
The format for nesting is to add another if statement after the last else statement.
The last tip for today is how to check a particular column has one of many values. In the previous example, I checked for two values in the Name column; I could also achieve this by using List.Contains():
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/193/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
The M code for the ‘if’ statement is:
if List.Contains( {"Mary", "John"}, [Name]) then "Yes" else if[More Companies.Head Company]= "Tentuniverse" then "maybe" else [Name] & " not Mary")
Here, instead of checking for ‘Mary’ and ‘John’ in separate clauses, I specify a list, and then check if the column Name contains any values in that list. The end result is the same.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/193/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
I can easily expand my list to contain more names or use another list query.
Come back next time for more ways to use Power Query!