You can add a conditional column to your query by using a dialog box to create the formula. 

Your browser does not support video.
  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

  2. Select Add Column > Conditional Column. The Add Conditional Column dialog box appears to help you create a syntactically correct formula:

    if condition then expression else expression

  3. In the New column name box, enter a unique name for your new conditional column. In the example, we change the name from Custom to Region.

  4. In the Column name list box, select a column name. In the  example, we select Name & Postal, which is a list of American states.

  5. In the Operator list box, select an operator. In the example, we select Contains.

  6. In the Value box, enter the appropriate value. In the example, we enter "Washington".

  7. In the Output box, enter the output value that your conditional column should display when the if condition is true. In the example, we enter "West".

  8. If you want to add an else expression when the condition is false, select Add Clause, and then repeat steps 4 to 6. In the example, additional regions are added including South, Northeast, and Midwest. This is commonly referred to as a nested condition.

    To delete or rearrange clauses, select More (...)  next to the clause, and select a command.  

  9. Optionally, add a final else expression. You can enter a value, another column, or a parameter.

  10. Select OK.

Notes   

  • By default, a conditional column doesn’t have a data type automatically defined. For more information, see Add or change data types.

  • To modify the condition, select the Edit Settings  Settings icon  icon next to the Added Conditional column step in the Applied Steps of the Query Settings pane.

See Also

Power Query for Excel Help

Add a custom column

Add a column based on a data type

Add a conditional column (docs.com)

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Thank you for your feedback!

×