In an earlier blog post, I reviewed Flash Fill as a fast, accessible AI-powered tool for pattern recognition in Excel.
Helpful though as it is, Flash Fill is not the most scalable solution — that it, it’s best for fast, one-off analyses. It does not develop explicit rules that can be extrapolated to new data.
For something like that, one option is Column from Examples in Power Query:
This data consists of a list of companies with their respective industries. My goal is to slightly recode these categories – for example, I want to change Technology to Tech, Consumer Discretionary to Consumer, and so forth, as show in column D of the following screenshot. Importantly, I want to do this in a way that if I add or change companies in the list, everything still works.
While a number of solutions could work here such as building a lookup table or build my own series of if statements, why not have Power Query do the heavy lifting?
Creating the column from example
To get started, head to Data > Get & Transform Data > From Table/Range. Highlight the Industry column as that’s the one we want to build an example from.
Next, choose Add Column from the Power Query editor home ribbon and select Column from Examples > From Selection:
You should now see a Column1 to the right of your dataset that is ready to be used for a column by example. To get started, it’s just like Flash Fill — start typing!
You’ll see that Power Query makes it best guess at how to complete the pattern across all rows of the table immediately. There will be some trial and error involved like any probability-driven AI application, but over time you should arrive at a sensible solution.
You can see the full solution, sped up below.
When you are done, rename this new column Industry Recode and load the results into Excel as a table.
Viewing the Column from Example rules
What just happened — how did this seeming AI magic work? To find out, head back to edit your query. Under Applied Steps to the right of your data, you should see a step called “Added Conditional Column.” Click the gearwheel and you’ll see the steps that Power Query applied to build this conditional column.
Here you’ll see AI at its most basic — a really long series of if/else statements.
Unlike Flash Fill, this has been encoded as a series of rules that can be applied to new data.
But keep in mind that new data might “break” these rules, or the rules might be appended. For example, what if one of the new companies added was “Consumer Durable?” That would not be captured and transformed by the rules. In this case, it might be better to build these rules out yourself, for example setting the filter to state that any cell where Industry starts with Consumer should be transformed.
This may seem naive, but think how long it may have taken you to parse these columns without AI’s assistance — I’ll take some guided if statements anytime!
I plan to explore other forms of AI as seen in Excel in later blog posts. But in the meantime, what questions do you have about Column from Examples? Have you used this feature before, and for what? Let me know in the comments.
Column from Examples and null
Column from Example is a powerful pattern recognition device, but it’s not omniscient. Take, for example, the next worksheet in the practice file, called contractors. Here we have a set of contracting businesses that we’d like to list as either plumbing, electrical or carpentry providers.
While these businesses by and large by their names make it clear which service they provide, Power Query has a hard time categorizing them through its pattern recognition techniques.
Go ahead and load the dataset into Power Query and run a Column from Example. Upon just a couple of rows, a returned set of nulls is returned. This means that Column from Example has given up on guessing.
While it’s possible that, like with other AI tools, providing the algorithm more data will lead it to actually sense a pattern, it’s not super likely. That said, using Column from Example to even manually set up these conditional rules can still prove a solid, beginner-friendly way to encode some conditions for data transformation.
I’ve gone ahead and completed that series of selection in the solution file, which you can access here:
Again, this series of if statements could be improved with some manual intervention, for example setting up a rule where any row that says “Electric” is labeled “Electrical,” but Power Query provides an easy, user-friendly way to at least get the ball rolling.
Column from example as rules-based AI
Artificial intelligence (AI) is a broad field with a dizzying number of applications and sub-disciplines. Even within Excel, a variety of features implement AI in their own ways. .
Leave a Reply