HOW TO: Add a Conditional Column to a Query

1. Select a cell in the query results worksheet and launch Power Query.
   • You can launch Power Query from the Get Data button [Data tab, Get & Transform Data group].
   • Alternatively, point to the query name in the Queries & Connections pane and click Edit in the information pop-up window.
   • A third option is to click the Query command tab and then click the Edit button [Edit group].
2. Click the Conditional Column button [Add Column tab, General group].
   • The Add Conditional Column dialog box displays.
3. Click the New column name box and type a name for the conditional column.
4. Click the Column Name drop-down list and select the field name for the IF expression.
   • A conditional column uses an existing column to build new data.
5. Select the operator.
   • The first part of the condition is like a logical_test argument in an IF function.
6. Click the Value box and type the expression.
   • Enter a value or text to be evaluated.
   • Click the drop-down list to the left of the Value box and choose Select a column to use another column in the expression.
   • When available, click the calendar icon to choose a date for the expression.
7. Click the Output box and enter the result.
   • The output is like the value_if_true argument in an IF function.
   • Click the drop-down list to the left of the Output box and choose Select a column to display results from another column.
8. Click Add Clause to add an Else If statement (a nested logical_test) as needed.
   • Click the ellipsis (…) for a row to delete or reposition the statement in the dialog box.
9. Click the Else box and enter the result (Figure 10-17).
   • The Else option is a value_if_false argument.
   • Click the drop-down list for Else and choose Select a column to display results from another column.
10. Click OK.
    • The conditional column displays at the right of the query.
11. Select the column and click the Move button [Transform tab, Any Column group].
    • Select Left to move the column one column to the left at a time.
    • You can move the column to the beginning and then to the right as desired.
    • Alternatively, drag the column heading to reposition it as desired.

HOW TO: Create a Column From Examples

1. Select a cell in the query results and launch Power Query.
   • You can launch Power Query from the Get Data button [Data tab, Get & Transform Data group].
   • Alternatively, click the Query tab and click the Edit button [Edit group].
   • You can point to the query name in the Queries & Connections pane and click Edit in the information pop-up window.
2. Select the column from which to build an example.
   • Select multiple columns as needed.
   • Build a column from all existing columns if necessary.
3. Click the Column From Examples button [Add Column tab, General group].
   • An empty column displays at the right of the window.
4. Click the first row in the empty column if necessary.
5. Type the first character for data in the new column (Figure 10-18).
   • You can type a new character to insert it in front of existing data.
   • Type the first character from the first item in an existing row.
6. Complete the example and press Enter.
   • The sample column fills if Power Query identifies your pattern.
   • The Add Column From Examples bar displays the M language code to build the column.
   • Type a second line if the pattern is not recognized or build the example using another field.
7. Click OK to complete the new column (Figure 10-19).
   • Rename and position the column as desired.