In Power Query, you can create a custom column that calculates values using a formula. In this example, you'll create a column that calculates a 2.5% bonus for sales over $25,000.
In Power Query Editor, select Add Column > Custom Column.
In the New column name box, enter Bonus.
In the Custom column formula box, enter the following formula:
if [Total Sales] > 25000 then [Total Sales] * 0.025 else 0
This formula calculates a 2.5% bonus when the value in the Total Sales column exceeds $25,000. Otherwise, it returns 0.
Tip
To insert a column reference into the formula, double-click the column name in the Available columns list.
Note
Check the bottom of the Custom Column dialog box for messages indicating whether the formula contains errors. For more information about Power Query formulas, select the link at the bottom of the dialog box.
Select OK to create the custom column.
To return the transformed data to an Excel worksheet, select Home > Close & Load.