Related topics
Get to know Power Query
Sign in with Microsoft
Sign in or create an account.
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.
Get to know Power Query

Create a conditional column

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

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.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!