Add a conditional column (Power Query)

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

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   icon next to the Added Conditional column step in the Applied Steps of the Query Settings pane.