Power Query: Setting an Example

Welcome to our Power Query blog. This week, I look at what I can do with the ‘Column from Examples’ feature.

The new feature ‘Column from Examples’ essentially pulls together a number of Power Query functions into one place, so it’s very user friendly. It’s ideal if I have an idea of what I want to do in my new column, but I’d like to try a few things.

In the Query Editor, the ‘Column from Examples’ can be found in the ‘Add Column’ tab:

I have the option of ‘From All Columns’, which allows me to create my column by using data from any of my columns or I can use ‘From Selection’, which will only allow my new column to transform data from the columns that I have selected. I want to have a look at what I can do with this feature, so I opt for ‘From All Columns’:

I am presented with a new empty column, which I can type into.  I try combining columns of different types to see what happens.  I type the following: 

This effectively combines my Amount (a currency amount) with my Description (a text field).  The instruction in the pane above my query tells me to type CTRL + ENTER to enter my value, but I find that ENTER works too. 

Not only has Power Query recognised what I am trying to do, it has filled in the rest of the values in my column so that I can check.  It’s like Flash Fill (Excel 2013 onwards) but with formulae!  The new column has now been given the name Merged, and the M code behind the transformation is displayed in the pane above my query. 

This is a satisfying result. I can choose to accept the values and move on or I can go back and type something else into the first row of my column.

Power Query tries to make sense of what I am entering as soon as I start typing. ‘20’ is likely to be a date, so various date functions are suggested. I find that I am able to type data in up to three rows before Power Query either follows the pattern of what I am doing or issues a message telling me that the transformation is not recognised (and to start again).

The transformations available are listed on the Microsoft help pages for the ‘Column from Examples’ description:

Number transformations

• Average
• Count distinct values
• Count values
• Minimum
• Maximum
• Median
• Percent of
• Power

Text transformations

• Text.Combine (supports combination of literal strings and entire column values)
• Text.Replace
• Text.Start
• Text.Middle
• Text.End
• Text.BeforeDelimiter
• Text.AfterDelimiter
• Text.BetweenDelimiters

Date transformations

• Date.Day
• Date.DayOfWeek
• Date.DayOfWeekName
• Date.DayOfYear
• Date.Month
• Date.MonthName
• Date.QuarterOfYear
• Date.WeekOfMonth
• Date.WeekOfYear
• Date.Year
• Date > Age
• Date > Year > Start of Year
• Date > Year > End of Year
• Date > Month > Start of Month
• Date > Month > End of Month
• Date > Quarter > Start of Quarter
• Date > Month > Days in Month
• Date > Quarter > End of Quarter
• Date > Week > Start of Week
• Date > Week > End of Week
• Date > Day > Day of Month
• Date > Day > Start of Day
• Date > Day > End of Day

Time transformations

• Time.Hour
• Time.Minute
• Time.Second

Date/Time transformations

• Subtract days
• Combine Date and Time
• Earliest
• Latest
• Subtract time

This is a substantial list for now, but I will be hoping for more transformations to be added soon!

Back in my query, I decide to go ahead and create a Merge column which combines my Amount and my Description columns. I click ‘OK’ in the pane above my query, and my new column appears in the query. The transformation step is added as ‘Inserted Merged Column’:

I think one of the main benefits of this feature is that it allows access to a lot of M code functions without me needing to understand M code or needing to know where all the separate functions are on the Ribbon.  I look forward to seeing more functions added to this facility in due course.

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here.  Come back next time for more ways to use Power Query!