Contents
One of the most viewed videos on my YouTube channel is a video about Flash Fill in Microsoft Excel. The essence of this tool is that if you need to somehow transform your source data, then you just need to start typing the result you want to get in the adjacent column. After several manually typed cells (usually 2-3 are enough), Excel will “understand” the logic of the transformations you need and automatically continue what you have typed, completing all the monotonous work for you:
The quintessence of efficiency. The magic “do it right” button that we all love so much, right?
In fact, there is an analogue of such a tool in Power Query – there it is called Column from examples (Column from Examples). In fact, this is a small artificial intelligence built into Power Query that can quickly learn from your data and then transform it. Let’s take a closer look at its capabilities in several practical scenarios in order to understand where it can be useful to us in real tasks.
Example 1. Gluing/cutting text
Let’s say we have such a “smart” table in Excel with data on employees:
Load it into Power Query in the standard way – with the button From Table/Range tab Data (Data — From Table/Range).
Suppose we need to add a column with last names and initials for each employee (Ivanov S.V. for the first employee, etc.). To solve this problem, you can use one of two methods:
- right-click on the column heading with the source data and select the command Add column from examples (Add column from examples);
- select one or more columns with data and on the tab Adding a column choose a team Column from examples. Here, in the drop-down list, you can specify whether all or only selected columns need to be analyzed.
Then everything is simple – in the column that appears on the right, we begin to enter examples of the desired results, and the artificial intelligence built into Power Query tries to understand our transformation logic and continue further on its own:
By the way, you can enter the correct options in any cells of this column, i.e. not necessarily top-down and in a row. Also, you can easily add or remove columns from the analysis later using the checkboxes in the title bar.
Pay attention to the formula at the top of the window – this is what smart Power Query creates to get the results we need. This, by the way, is the fundamental difference between this tool and Instant fill in Excel. Instant filling works like a “black box” – they don’t show us the logic of the transformations, but simply give ready-made results and we take them for granted. Here everything is transparent and you can always absolutely clearly understand what exactly is happening with the data.
If you see that Power Query “caught the idea”, then you can safely press the button OK or keyboard shortcut Ctrl+Enter – a custom column with a formula invented by Power Query will be created. By the way, it can be later easily edited as a regular manually created column (with the command Adding a Column – Custom Column) by clicking the gear icon to the right of the step name:
Example 2: Case as in sentences
If you right-click on the column heading with text and select the command Transformation (Transform), then you can see three commands responsible for changing the register:
Convenient and cool, but in this list, for example, I personally have always lacked one more option – case as in sentences, when capitalization (capital) becomes not the first letter in each word, but only the first letter in the cell, and the rest of the text when This is displayed in lowercase (small) letters.
This missing feature is easy to implement with artificial intelligence Columns from examples – just enter a couple of options for Power Query to continue in the same spirit:
As a formula here, Power Query uses a bunch of functions Text.Upper и Text.Lower, converting text to upper and lower case, respectively, and functions Text.Start и Text.Mid – analogues of the Excel functions LEFT and PSTR, able to extract a substring from the text from the left and from the middle.
Example 3. Permutation of words
Sometimes, when processing the received data, it becomes necessary to rearrange the words in the cells in a given sequence. Of course, you can divide the column into separate word columns by the separator and then glue it back in the specified order (don’t forget to add spaces), but with the help of the tool Column from examples everything will be much easier:
Example 4: Only numbers
Another very vital task is to pull out only numbers (numbers) from the contents of the cell. As before, after loading data into Power Query, go to the tab Adding a column – Column from examples and fill in a couple of cells manually so that the program understands what exactly we want to get:
Bingo!
Again, it’s worth looking at the top of the window to make sure Query generated the formula correctly – in this case it contains a function Text. Select, which, as you might guess, extracts the given characters from the source text according to the list. Subsequently, this list, of course, can be easily edited in the formula bar if necessary.
Example 5: Text only
Similarly to the previous example, you can pull out and vice versa – only the text, deleting all numbers, punctuation marks, etc.
In this case, a function that is already opposite in meaning is used – Text.Remove, which removes characters from the original string according to a given list.
Example 6: Extracting data from an alphanumeric porridge
Power Query can also help in more difficult cases, when you need to extract useful information from the alphanumeric porridge in a cell, for example, get the account number from the description of the payment purpose on a bank statement:
Note that the Power Query generated conversion formula can be quite complex:
For ease of reading and understanding, it can be converted into a much more sane form using a free online service. Power Query Formatter:
Very handy thing – respect to the creators!
Example 7: Converting dates
Tool Column from examples can be applied to date or datetime columns as well. When you enter the first digits of a date, Power Query will helpfully display a list of all possible conversion options:
So you can easily convert the original date to any exotic format, such as “year-month-day”:
Example 8: Categorization
If we use the tool Column from examples to a column with numeric data, it works differently. Suppose we have employee test results loaded into Power Query (conditional scores in the range 0-100) and we use the following conditional gradation:
- Masters – those who scored more than 90
- Experts – scored from 70 to 90
- Users – from 30 to 70
- Beginners – those who scored less than 30
If we add a column from the examples to the list and start arranging these gradations manually, then very soon Power Query will pick up our idea and add a column with a formula, where operators nested into each other if logic will be implemented, very similar to what we need:
Again, you can not press the situation to the end, but click on OK and then correct the threshold values already in the formula – it’s faster this way:
Conclusions
Certainly a tool Column from examples is not a “magic pill” and, sooner or later, there will be non-standard situations or especially neglected cases of a “collective farm” in the data, when Power Query will fail and will not be able to work out what we want correctly for us. However, as an auxiliary tool, it is very good. Plus, by studying the formulas he generated, you can expand your knowledge of the functions of the M language, which will always come in handy in the future.
- Parsing Text with Regular Expressions (RegExp) in Power Query
- Fuzzy text search in Power Query
- Flash Fill in Microsoft Excel