How to Use Column From Examples in Power Query

Step-by-step

Column From Examples works like Flash Fill: you type a few examples and Power Query works out the pattern. The difference is that Power Query writes the rule down, so you can check it, edit it and reuse it every time the data changes. The example recodes a list of companies’ industries: Technology becomes Tech, both Consumer Discretionary and Consumer Staples become Consumer, and so on.

1. Load the data into Power Query

Click inside the list and go to Data > From Table/Range. Confirm the headers and the Power Query editor opens.

2. Start a column from examples

Select the Industry column, then go to Add Column > Column From Examples > From Selection. Using From Selection means only the Industry column is used, not the company name. A blank Column1 appears.

3. Type a few examples

In the first row type Tech. Power Query fills the rest based on what it’s guessed so far. Correct any rows that are wrong: type Consumer, Healthcare, and change Consumer Staples to Consumer too. Each correction refines the rule. Check that every Technology row now says Tech and both consumer categories say Consumer.

4. Check the rule it wrote

Click OK. In Applied Steps, click the gear icon on the Added Conditional Column step to see the rule as plain if/then conditions. Edit them if anything needs adjusting.

5. Tidy and load

Rename the new column Industry Recode, right-click the original Industry column and choose Remove, then Close & Load.

6. Refresh when the data changes

Add a new company to the source table, right-click the result and choose Refresh: the new row is recoded automatically. That’s the advantage over Flash Fill, which is quicker for a one-off but can’t be reused.

Go further

My Excel Power Query, Power Pivot, DAX & Power 3D Map course goes much further into cleaning and reshaping data with Power Query.