We’ve all been there: struggling with a nested Excel formula that breaks when your data shifts, or using Flash Fill only to find it missed half the rows. I stopped relying on those fragile workarounds and switched to Power Query’s Column From Examples for more reliable automation.
Why Column From Examples is a better way
Trade flaky tools for stable automation
For years, the most common way to clean data was to build nested MID, LEFT, and FIND formulas that were a nightmare to troubleshoot. When Excel introduced Flash Fill, it felt like a miracle because it replaced those formulas with simple pattern matching. But Flash Fill doesn’t stay linked to your data, and it often misinterprets complex patterns without warning.
Power Query’s Column From Examples is a more robust, flexible alternative to both methods. It gives you the ease of Flash Fill with the structural integrity of a formula. Instead of a one-time guess, it builds a reusable transformation. If your source data updates, you don’t have to rewrite a formula or “re-flash” your column—you just click Refresh.
Prepare your data and launch the editor
Power Query works best when your data is formatted as an Excel table (Ctrl+T), as it’s contained in a single object that expands as you add rows.
In the examples below, I’ll use a table named T_MasterLogs. To follow along as you read this guide, download a free copy of the workbook containing this table. When you click the link, you’ll find the download button in the top-right corner.
To get started, click anywhere inside the table, then head to the Data tab on the ribbon and select From Table/Range. This opens the Power Query Editor in a new window.
Stop counting characters and let the tool find the pattern
Column From Examples infers patterns in long strings of text and isolates them without requiring you to define the starting or ending character positions.
The scenario: The first column of your T_MasterLogs table contains long strings with an embedded five-digit ID. You need to pull out this ID, but its position shifts depending on the length of the month name.
Here are the steps you need to take:
- In the Power Query Editor, open the Add Column tab and click Column From Examples.
- In the new empty column, type the ID from the first row (88392), then press Enter.
- If Power Query makes accurate suggestions in the rows below, click OK to confirm.
- Rename and reposition the new column.
Even if Power Query correctly guesses the remaining IDs, I recommend overtraining the tool. If you have a large dataset, scroll down to a row with a different structure, then type the ID manually for that row as well. This encourages Power Query to infer a more flexible transformation that is less likely to break when your data shifts.
When you finish this step, Power Query writes a background script in a language called M. On your first attempt, it might have simply looked for a specific delimiter, like a dash. If your data later uses a different format, it might fail. But don’t worry—you can fix it quickly:
- Before starting over, try adjusting the step via the settings cog in the Applied Steps pane.
- If that doesn’t resolve the issue, delete the step and retrain it with more diverse examples.
5 everyday actions that Power Query does better than regular Excel tools
Replace manual Excel tasks with conditional columns, smart merging, the unpivot tool, and more.
Transform chaotic columns into professional data
The Column From Examples tool acts as a formatting engine that can combine multiple columns or fix casing by observing the result you want.
The scenario: In the same T_MasterLogs table, you have “First Name” and “Last Name” columns where the casing is all over the place. You also have a couple of tricky entries—”o’connor” and “d’angelo”—that need a capital letter after the first apostrophe. Your aim is to generate a single “Full Name” column.
This sounds complicated, but the workflow is simple:
- In the Power Query Editor, hold Ctrl and select both the First Name and Last Name columns.
- Click Column From Examples > From Selection.
- In the first row of the new column, type John Smith and press Enter.
Now, look at the suggestions for the rows below. Power Query will likely identify the merge and proper-casing logic immediately. In my case, it even correctly capitalized the “C” in O’Connor and the “A” in D’Angelo without being told.
That said, if a specific name (like a hyphenated surname) looks wrong, simply click that cell and type the correct version to fine-tune the tool’s logic.
Once the suggestions look correct for all rows, click OK to add the new column. Rename the new column, then right-click the old, messy ones to Remove them.
Because Power Query applies transformations through recorded steps, you can safely remove intermediate columns after using them to build your final output.
The big payoff: Refreshing your data
Watch the magic happen
You really see the benefit of Power Query after finishing your examples. Unlike Flash Fill, which you have to re-run manually every time you add new data, Power Query saves the sequence of steps you just built.
When you’re happy with your new columns:
- In the Home tab, click the top half of the Close & Load button to drop your cleaned data into a brand-new worksheet.
- Try adding a new, messy row to your original T_MasterLogs table (like a name in all lowercase or a new ID string).
- Go back to your cleaned results table, right-click anywhere, and click Refresh.
Excel then runs the new data through the M logic you created earlier to extract the ID and fix the casing for the new rows automatically. This is especially useful when importing data that’s consistently structured but inconsistently or untidily formatted.
Remember the golden rule I mentioned earlier: if your source data changes drastically and the refresh fails, don’t panic. Simply go back into the editor, delete the step in the Applied Steps pane, and start the process again to retrain the engine on the new pattern.
You don’t need VBA to auto-refresh your Power Queries in Excel
Stop relying on manual clicks and clunky code—let Excel refresh your queries automatically.
Working in Excel for the web? There’s another option
Power Query’s full suite of features is mostly reserved for people using desktop versions of Excel. If you launch it in Excel for the web, you’ll notice a more limited feature set. But don’t fret—there’s another way to achieve a similar outcome. Formula by Example is a powerful workaround specifically built for the web, and (at the time of writing) it doesn’t require a Copilot subscription. Just make sure your data is in an Excel table, and you’re good to go.
Either way, whether you’re isolating nested IDs in Power Query or using Formula by Example, it’s a crucial step that moves you past the era of fragile formulas and unreliable Flash Fill. Once you try it, you’ll likely never go back.
- OS
-
Windows, macOS, iPhone, iPad, Android
- Free trial
-
1 month
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.
Stephan is the sports journalist for the Maple Grove Report.
