Regardless of the software you use, data prep sometimes requires you to have at least a rudimentary level of proficiency in the language it uses. Sometimes, however, creating a new calculated field might require a higher level of fluency, sending you off to a journey of seeking wisdom from the online data prep community if you want to calculate what you intended. This on the other hand, although helps you learn something new, might take some time which sometimes is of essence.

This is where the Column From Examples option in Power Query comes in. Allowing you to transform a field you already have (or to create a new one), this option attempts to create a new calculation for you based on few data entry examples you provide. You can find it in the Transform or Add Column tabs (see below):

Sounds interesting, right? Let’s look at an example.

In this scenario, I’m trying to create a new column, specifying the size of the product (Small, Medium, Large) using the letter (S,M,L) at the end of each entry in ‘ProductName' column. (One way I could approach this task is using Conditional Column, but Column From Examples offers a solution as well!)

To use it, I need to select the ‘ProductName’ column, go to Add Column → Column From Examples → From Selection.

Clicking that opens up a new window with a new Custom column, allowing me to entry the product sized in appropriate columns (e.g. for Men’s Bib Shorts M - Medium). After putting in only 3 entries, you can see that Power Query already populated the rest of the column, correctly identifying where the sizes. The program did it based on the calculation visible at the top.

If you’re satisfied with the outcome, all you have to do is click OK and it’s ready!

A couple things to keep in mind:

Bear in mind that this functionality has its limitations. Depending on what you’re trying to achieve, giving to many conditions in your Custom entry might break Powe Query’s guessing abilities. Ideally, try keeping your entry conditions as simple as possible.

Even if you’re not fluent in PQ’s calculations and syntax, try to sense-check the calculation it shows above the table. Sometimes, even if at first glance the populated table looks good, you might notice from the calc window that the formulas used won’t work for all of the data.

Remember that you might have more unique values than you see in the window! Looking at the example above, imagine I had products with XL or XS sizes. Based on the entries I put in, the formula created by PQ would not populate those with Extra Large or Extra Small - instead it would insert a null value. This is why it’s important to check what kind of unique values you have in the column (in the entire dataset!) before going into making any conditional changes!