Adding in Conditional Columns
Adding in Conditional Columns
Previous Session Summary
Lesson Synopsis
Resource Listing
The Video
- 00:50 - What are IF statements in Standard Excel?
- 01:29 - A practical example of an Excel IF statement
- 02:50 - How can I create a conditional column (IF statement) in PowerQuery?
Lesson Notes
In this lesson we’re going to look at how to add a basic conditional column into a table in PowerQuery. Chances are that if you’re learning about PowerQuery, you know a reasonable amount about Excel. If that’s the case, you have probably come the ‘IF’ function and the good news is that PowerQuery has its own version of the IF statement. Being PowerQuery it’s easier to perform than writing an IF statement in Excel.
What is an IF statement in Standard Excel?
Just in case you haven’t come across IF statements before, or you’d like a reminder, this is what it does and how it works:
IF functions are designed to populate a cell or column with one value depending on whether a logical argument is true and another value if the logical argument is false. So let’s have a quick look at the IF statement syntax.
= IF(Logical Argument, Value if Logical Argument is True, Value if Logical Argument is False )
The IF statement has three parameters
a) the first parameter is the logical argument that we’re evaluating,
b) the second parameter is the value that’s returned if the logical argument is evaluated as true,
c) while the third parameter is the value returned if the logical argument is evaluated as false.
So that’s the theory, lets have a look at a more practical example.
= IF( A1 < 100, “Possible”, “Impossible” )
In this practical example, if the value in cell A1 is less than 100 then the IF function will return the word “Possible”. This is because the logical condition evaluated in the first parameter has been found to be true. If the value in cell A1 is not less than 100, then the value “Impossible” is returned because the logical condition has been found to be false.
How to write an IF statement or Conditional Column in PowerQuery
IF statements aren’t particularly complex but they can be made easier using PowerQuery. All you need to do is click a button and fill in the blanks in a menu. So how do we do it?
a) the first thing to be done is to go to the Add column tab.
b) Go to the General section on the ribbon
c) select the Conditional column button. This brings up the Add Conditional Column menu.
d) Next, in the first available blank window fill in the new column’s name, much like we do when adding a Custom Column.
In this Add Conditional Column menu, we need to play a bit of ‘Fill in the Blanks’. The Add Conditional Column menu basically is as follows:
If ‘Column W’ equals ‘X’ then put in ‘Y’ otherwise put in ‘Z’.
You can customise parts X-Z with alphanumeric values, column names or parameters (we haven’t covered these yet!) You can also change the equals operator (which is in italics above) to be any operator if W is a numeric column. If W is a column name, then PowerQuery will allow you to search for values in that column that start or end with certain letters which you can define in X.
All rather theoretical – here’s what you need to do.
e) Populate the first parameter of our IF argument, which is Column W in the above equation. This needs to be the column with the text or number that we’re logically testing. Simply select the column you want from the drop down menu marked ‘Column’.
f) Next change the operator, so for example equals (=), does not equal (<>), more than (>) or less than (<). You don’t need to know the operator command – again just select from the drop down menu marked ‘Operator’.
g) After that we can select the value, column or parameter (we haven’t covered these yet!) against which we’d like to select the information in Column W. This is part X of the equation above. Firstly define whether you’re going to put in an alphanumeric value or choose a column name or parameter. You can do this using the drop down that’s currently marked ‘Value’ and populated with ‘ABC123’ for alphanumeric values.
h) Depending on what you selected in the previous step, next type in your alphanumeric value or select your column name of parameter.
a) Next, put in what value or column you would like the new column to return if the logical condition is true (so in the equation if the value in Column W equals X). Again you can choose an alphanumeric value, a column or a parameter.
b) Finally put in the value you would like to return if the logical condition is false (so in the equation if the value in Column W does not equal X).
Once you’ve filled in your blanks and selected ‘OK’ your new column will appear at the end of the data table.
.... and a little more!
Operators – Operators in PowerQuery are pretty much the same as in Standard Excel. Here’s a quick list for you:
= – Equals
<> – Does not Equals
> – More than
< – Less than
>= More than or equals to
<= Less than or equals to
Please note that => will not do the job for ‘More than or equals to’ as this is a way of defining a custom function in PowerQuery!