Sometimes you’re looking to create a calculated field in Power BI, but don’t know the proper syntax to use to accomplish it.  How do you change the order or characters? How do you insert special characters or remove others?  The Column from Example field can be a quick and easy way to accomplish this. You tell Power BI how you want the field to look, and Power BI does the hard work for you!

Here’s a good example.  The Author.Name field below displays names in a less-than-user friendly manner.  But it still contains valuable information.  To use this column as your example, first go into Query Editor.  Once there, select the table, then select the column by clicking once on it’s column header.

Within Query Editor, on the Add Column tab, select “Column from Examples”.

From there, A new column with the header “Custom” will be added.  You will type in this column the format in which you’d like the existing column to display.  In my column, I decided to use “Last, First”, so names could be sorted in the report by last name.  Because there were different names in the list, I chose a few so I’d be sure the resulting formula accounted for all possibilities.  I typed the names by the arrows below for Scott, Elena, and Joe.  Once I typed Scott’s name, the others auto-populated. To be sure, though, I didn’t just want to use one name as an example, so I did all three.  This would be particularly helpful if you had some names with middle initials that you wanted to drop so the syntax would factor those in.

For good measure, I then rename the column so it’s easy for me to find to bring into report visualizations.

Finally, for the learning opportunity, I like to look at the formula that Power BI so graciously created for me.  I do this by clicking on the “Added Custom Column” step in the Applied Steps panel, and expanding the edit panel. As you can see from what was created, that custom column would have been quite the handful to create myself!