Use Column From Examples to Learn Power Query Formulas

February 28, 2023 - by Bill Jelen

Problem: I need to add a new column in Power Query. The formula syntax is difficult to learn.

Strategy: Use Column From Examples and Power Query will teach you the syntax.

The formula language in Power Query is the one frustration that Excellers will have with Power Query. In Excel, you can type a formula in lower case or upper case. Excel does not care if you type =vlookup or =VLOOKUP or =VLookUp - all of those formulas will work. In Power Query, the function names are different and they are case sensitive. A formula such as =MID(A2,5,3) is different in Power Query.

During 2018, Microsoft added a new Column From Examples feature to Power Query. At first, it seems similar to Flash Fill in Excel, but there is one important difference. Flash Fill is a one-time operation. Things in Power Query have to be refreshable. So, when you use Column From Examples in Power Query, the feature will write a formula that will work with future data sets.

Flash Fill was added to Excel 2013. There were a few long-term members of the Excel team who had left the team during the Office 2013 cycle to pursue other opportunities. When they came back to the Excel team and saw Flash Fill was not repeatable, getting Column From Examples became a priority for them.

In the next screenshot, you will see a column where you want to extract the three characters starting at the sixth position of the code. In Excel, this formula would be =MID(C2,6,3). As I write this section, I honestly can not remember the equivalent Power Query function. It might be Text.FromPosition or Text.FromMiddle or something like that. To learn the correct syntax, use Column From Examples.

Select the Code column in Power Query. From the Add Column tab, choose Column From Examples.

You want to extract the three characters starting at position 6 from the Code column. Select that column. In Power Query, choose the Add Column tab and then the Column from Examples icon. — Figure 1037. Select the column that contains the data to be transformed.

A new column appears on the right side of the Power Query window. Type 887 in the first row. Power Query guesses what you mean and guesses wrong. Type 406 in the second row. The heading changes to say Text Range and the rest of the values appear in grey font. This looks correct.

A column appears on the far right where you can type some examples. Type the correct code for the first two rows. The grey guesses for the other rows appear to be correct. — Figure 1038. After you type two examples, Power Query understands what you are trying to do.

Look in the top left of Power Query and a formula will appear saying that Ctrl+Enter will apply Text.Middle([Code],5,3).

The message Add Column From Examples shows that the correct formula is Text.Middle([Code], 5, 3). — Figure 1039. Power Query shows you the formula to use.

Press Ctrl+Enter and you will have the new column.

The new column appears in the query. — Figure 1040. The column is added to Power Query.

For another example, what if you needed to convert the date column to show Month, Year, Weekday, First of Month, Last of Month? There are Excel formulas for all of these and there is a good chance that you know these formulas: =MONTH(), =YEAR(), =TEXT(C2,"DDDD"), =EOMONTH(C2,-1)+1, =EOMONTH(C2,0). However, the functions in Power Query are completely different.

Column From Examples comes to the rescue. Select the Date Column and choose Column From Examples.

The date in the first row is 9/13/2024. As soon as you type a 9 in the Column from Examples column, Power Query offers many choices. You can choose from the choices to get the month number, end of month, start of month plus many other things that would be difficult in Excel.

Using Column from Examples. With a date of 9/13/2024, type an example of 9 and the drop-down offers many choices, including End of Month from Date, End of Quarter from Date, Month from Date, Start of Month from Date, Start of Week from Date. Many of these would be tricky in Excel. — Figure 1041. By simply typing the 9 from 9/13/2024, you have all of these choices.

If you type F instead of 9 in the Column from Examples column, Power Query offers Friday, September, and many other calculations that would be difficult in Excel.

In Column From Examples, start to type F, and the suggestions include 5 for Day of Week from Date, Friday, 257 for Day of Year from Date, 30 for Days in Month from Date, 3 for Quarter of Year from Date. — Figure 1042. Column From Examples offers many choices that are difficult in Excel.

This article is an excerpt from Power Excel With MrExcel

Title photo by Volodymyr Hryshchenko on Unsplash