Conditional Columns

Using the Power Query Conditional Columns functionality is a great way to add new columns to your query that follow logical if/then/else statements. This concept of if/then/else is common across many programming languages, including Excel formulas. Let's review a real-world scenario where you would be required to do some data cleansing on a file before it can be used. In this example, you will be provided a file of all the counties in the United States, and you must create a new column that extracts the state name from the county column and places it in its own column:

1. Start by connecting to the FIPS_CountyName.txt file that is found in the book files using the Text/CSV connector.
2. Launch the Power Query Editor, and start by changing the data type of Column1 to Text. When you do this, you will be prompted to replace an existing type conversion. You can accept this by clicking Replace current.
3. Now, on Column2, filter out United States from the field to remove this value from the column.
4. Remove the state abbreviation from Column2 by right-clicking on the column header and selecting Split Column | By Delimiter. Choose -- Custom -- for the delimiter type, and type ,, then click OK:

5. Next, rename the column names Column1, Column2.1, and Column 2.2, to County Code, County Name, and State Abbreviation, respectively.
6. To isolate the full state name into its own column, you will need to implement a Conditional Column. Go to the Add Column button in the ribbon and select Conditional Column.
7. Change the New column name property to State Name and implement the logic If State Abbreviation equals null Then return County Name Else return null as shown in the following screenshot.  To return the value from another column, you must select the icon below the text Output, then choose Select a column. Once this is complete, click OK:

This results in a new column called State Name, which has the fully spelled-out state name only appearing on rows where the State Abbreviation is null. 

This is only setting the stage to fully scrub this dataset. To complete the data cleansing process for this file, read on to the next section. However, for the purposes of this example, you have now learned how to leverage the capabilities of the Conditional Column transform in the Power Query Editor.