I knew that the create Column From Examples feature had been released to Power BI Desktop, but it just showed up in my Excel 2016 build today.  And man is it cool!

A scenario for Create Column From Examples

A user on my forum asked how to build a function that would extract the "Show Name" from the following format:

DV1511H, Episode Name ( Show Name, SeriesNumber)

As the user pointed out, this can be done via the following Excel formula:

=LEFT((MID(A1,SEARCH(" ( ",A1)+3,200)), (SEARCH(", S",(MID(A1,SEARCH(" ( ",A1)+3,200)))-1))

But how do you do something similar in Power Query?

My initial thought

My first expectation was to use one of the techniques from M is for Data Monkey, using the equivalents of Excel's SEARCH and MID functions, basically emulating the Excel logic.  And while that would totally work, I got distracted by something when I opened up my Excel today:

The new create Column From Examples button was there on my ribbon.  So naturally, I had to see what it would come up with.

How to create Column From Examples

Naturally, it starts with clicking the created Column From Examples button, which gives you two options:

1. From All Columns
2. From Selection

In this case, they would do the same thing, but I'm going to choose "Selection" anyway, as I only need to look at one column.  When I do, I get a new message across the top, and a new column.  I put in the pattern I wanted to get:

And once I hit Enter, it actually shows me the pattern it used:

As I'm happy with it, I click OK.  The formula that it provided, (which I passed on to the questioner,) is:

Text.BetweenDelimiters([Column1], " ", ",", 3, 0)

Potential Improvements for create Column From Examples

Let me first say that I think this is fantastic.  I would not have come up with this function on my own, as I didn't even know that this function existed.

Some things that I wish we could change though:

1. The column is created with a generic name.  I really wish we could have changed this during the creation phase instead of ending up with the generic "Part of Column 1" text.  In order to fix this, we either need to edit the M code formula or do another rename step, both of which could be avoided if we could simply rename the column during the creation phase.
2. There is no gear icon in the applied steps window to take us back into the interface.  I'm sure that would be really hard to implement, but if you mess it up today, well… delete it and try again.
3. There is no way to copy the function during the creation phase, and with the gear icon not available, the only way to copy/change the formula is via the formula bar.  Not a big deal if you know your M code, but for a novice/intermediate user picking out the correct parts with all the commas, quotes and parenthesis here could be a bit tricky.

Overall, despite what I would change here, this is a fantastic new function that is going to make life a lot easier for people.  Very cool!