Power Query techniques: Conditional columnsby
Simon Hurst looks at conditional columns and the Power Query equivalent of the well-known IF() function.
IF() is one of the most frequently used Excel functions. The Power Query editor includes an equivalent if…then…else structure. This can be entered directly as a formula for a Custom Column, but a year or so ago a specific Conditional Column command was added to the General group of the editor Add Column Ribbon tab to make it easier to create the required formula.
Note: the Power Query tools started off as an optional add-in for Excel version 2010 and 2013 before they became an integral part of Excel 2016 as the Get & Transform group of the Data Ribbon tab.
The same tools can be found in the External data group of the Home Ribbon tab of Power BI Desktop. Throughout this series we will refer to all of these tools as 'Power Query' and base our instructions on the use of Excel 2016.
This is the equivalent of the Excel IF() function. The 'If' section evaluates a statement and returns TRUE or FALSE. If the result is TRUE the 'then' part of the formula is used; if the result is FALSE the 'else' part is used.
In this example we are checking to see if the FreightCompanyName field is equal to "Speedy Express". If it is we will set the value of the new custom column to 10 and, if not, we will set it to 15:
Here, we have set the name of our new custom column to FreightCharge. We can choose the column containing the value for our comparison, the type of comparison operator and then whether our comparison value is an entered value, whether it comes from the same row of another column or whether it uses a Parameter. For columns and parameters, you can choose valid entries from a dropdown list.
The same choices are available for our Output (then) and Otherwise (else) values. In our case we have compared the value in our FreightCompanyName column to the entered text value: Speedy Express, returning the value 10 if it matches and 15 if it doesn't. Note that, like almost everything in Power Query, our comparison test is case sensitive. Speedy express will not match Speedy Express. This differs from Excel comparisons which are, by default, not case sensitive, so in an Excel formula:
would return 10 whether R16 contained Speedy express, Speedy Express or speedy express.
Power Query uses the information typed in the Add Conditional Column screen to create the if…then…else formula:
Once our formula has been created, we can edit it directly in the formula bar, or use the 'gear' icon to the right of the APPLIED STEP name to reopen the original dialog. If you create or edit the formula directly you need to be careful with text case in all parts of the formula, not just the comparison text. FreightCompanyname won't find the FreightCompanyName column and using If rather than if will also generate an error.
In Excel, you can embed one IF() function inside another to return different values for different tests:
=IF(R16="Speedy express",10,IF(R16="United Package",20,15))
The Add Conditional Column dialog lets you add else if lines to achieve the same result. Clicking on the Add Rule button adds the else if line which contains the same options as the original if line:
Multiple else if statements can be entered and, once again, the dialog will create the required formula for us. Here we have added two else if lines to identify United Package and then Federal Shipping:
= Table.AddColumn(#"Removed Columns1", "FreightCharge", each if [FreightCompanyName] = "Speedy Express" then 10 else if [FreightCompanyName] = "United Package" then 20 else if [FreightCompanyName] = "Federal Shipping" then 25 else 15)
Although using a conditional column in this way is quite straightforward, once you start needing to enter multiple else if lines, it might well be preferable to construct a separate table and create the FreightCharge column by merging tables. The conditional column approach becomes increasingly unwieldy as we add more freight companies. In addition, making any changes to names or values or adding new companies will need someone to make changes within the Power Query editor.
Instead of this, we can create a simple table in our Excel workbook and use the From Table command to read it into our Query Editor:
We can then use the Merge Queries command in the Combine group of the editor Home Ribbon to merge our tables:
The FreightCompanyName field in each table creates the link and we set the Join Kind to 'Left Outer' to make sure out output table continues to include any rows from our Invoice table that don't have a matching value in our FreightCharges table. Join Kinds were covered in detail in a previous article: Power Query techniques – Merging tables of data.
We can now expand our resulting Freight Charges, Table column to show all of our FreightCharge values:
As we can see, the conditional column and the merged table both produce the same results. However, if we needed to add another freight company, the table approach would allow the user to add the details to the existing table and refresh the data without needing to make any changes to the Power Query steps or even open the query editor.
It is not always possible to create the entire formula using the Add Conditional Column dialog. For example, you might want to use an expression as part of your condition. You can use the Custom Column command from the Add Column Ribbon tab to create such an expression, but it might be easier to use the Add Conditional Column dialog to create most of what you need and then to edit the resulting formula. This was the first expression we created to check for Speedy Express:
= Table.AddColumn(#"Removed Columns1", "FreightCharge", each if [FreightCompanyName] = "Speedy Express" then 10 else 15)
We could adapt this code to check for first five characters in the name, rather than the whole thing:
= Table.AddColumn(#"Removed Columns1", "FreightCharge", each if Text.Start([FreightCompanyName],5) = "Speed" then 10 else 15)
Note that once you have changed your code in this way you will no longer be able to edit it in the Add Conditional Column dialog but will need to use the basic Custom Column dialog or the formula bar instead.