Create Column From Examples

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:

image

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:

image

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

image

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.

image

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!

‘DIY BI’ e-Book Launches Tomorrow!

Last week I announced that we are working on a series of free 'DIY BI' e-Books.  We've been hard at work on polishing it up, and I'm pleased to announce that the first DIY BI e-Book launches tomorrow!  It will be emailed at 9:00 AM Pacific Time to everyone on our newsletter list.

Sign up to get the free 'DIY BI' e-Book series

If you haven't already, sign for our mailing list to receive your copy!  You can do so at the bottom of this post.

Creating the 'DIY BI' e-Book

I'm really thankful that I have a team of people behind me for this.  For me, technical writing is actually the easy part.  It certainly takes time, don't get me wrong, but the magic of copy editing, proof reading and graphic design is a whole other story.

Deanna has done a great job of proofing the book, and making me re-write any paragraphs that sounded good in my head, but maybe didn't translate so well beyond that.  And Rebekah has done a phenomenal job on the graphic design and layout.

Each book will be themed as shown below:

image

Blue for Excel, based on the Excelguru website colour scheme.  Dark green for Power Query (like the powerquery.training site), light green for Power Pivot (like the Power Pivot logo) and yellow for Power BI like it's colour scheme.

The 'DIY BI' e-Book Cover

We wanted to create a cool cover, but most of the stock images for sale out there have a Mac in the picture.  Since 3/4 of these technologies won't work on the Mac, that plainly wasn't something we wanted to put out there.  So that led to us staging our own photo shoot to generate our cover - which I'll admit is a lot harder than I thought it would be.  Here's the finished cover for the first 'DIY BI' e-Book.

image

Our next e-Book will use the same cover image, but will be themed in the dark green of the Power Query series.

And yes, before you all ask, that IS a Pie Chart in the bottom left. And no, I don't love pie charts.  But sometimes you have to have one, because your boss asks for it.  (Just don't expect to find one INSIDE any of the e-Books!)  Smile

Reserve Your Free 'DIY BI' e-Book Now

If you're already receiving out newsletter, there is nothing else you need to do.  It will show up in your inbox shortly after 9:00 AM Pacific time on Apr 7, 2017.  If you're not on our newsletter list yet though, just sign up. It's that easy!

Subscribe to our mailing list

* indicates required