Create a conditional column

A conditional column calculates a value from a formula. In Power Query, the term used is custom column. In this section, let’s create a 2.5% bonus calculation for all sales over $25,000. To do this, write a formula in Power Query's formula builder. Power Query formulas are similar to Excel formulas.

1. Select Add Column > Custom Column.

2. Enter "Bonus" in the New column name text box.

3. To enter a custom formula, in the Custom column formula section to the right of the equal sign, enter:
   
   If Total Sales > 25,000 then Total Sales * 0.025 else 0.
   
   Tip   To avoid entering table names, double-click the one you want from the Available columns list.
   
   Note   Check the bottom of the custom formula pane for messages from Power Query to see if you are successful or if there are issues with the formula. For more information about Power Query formulas, select the link at the bottom of the Custom Column dialog box.

4. To return the transformed data to an Excel worksheet, select Home > Close & Load.