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:
- From All Columns
- 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:
- 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.
- 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.
- 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!
2 thoughts on “Create Column From Examples”
Less than 2 weeks between Power BI and Excel 2016 getting the update, MS is upping their game!
I agree with Point # 2 that you made, and posted a similar comment to Miguel Llopis. His response was:
C Haas • 9 days ago
This is really cool! Any chance that the "Gear" button will be available? That way the transformation could be adjusted after the step is completed. You show editing the results around 11:45, but that's before you commit.
Miguel Llopis -> C Haas • 8 days ago
Thanks C - Think of "Add Column from Examples" as a design-time tool that allows you to define steps in a different way, but the output of that tool is a certain step, which may or may not provide an Edit experience in the PQ UX already.
For example, if the detected transform was "Replace Values" then you would get a gear icon that allows you to edit the settings for such step (i.e. text to find / replace with). There might be other cases where the detected transform is more complex, or not supported as a first-class UX operation (such as the last step in the video with multiple text combinations and other function calls). In those cases, the "gear icon" would allow you to edit the step as a Custom Column expression.
I can still see our want to get back to the creation step, but I now understand Microsoft's stance with their design decision.
Honestly, I'd be good with getting a custom column step. At least then it's a bit clearer to adjust it. Digging through the entire M step is a mess. 🙂