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:
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.
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.
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:
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:
If I view this in the Advanced Editor, I can see the M code:
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():
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.
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!
