Power Query

Create a conditional column

Create a conditional column

Your browser does not support video.

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.

A subscription to make the most of your time

Need more help?

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

×