Create a column from an example

Let’s add several columns to deconstruct different date types from the existing Order Date field. For example, you may want to identify the day of the week or spell out a date such as Monday, January 15th.

1. Select the "Order Date" column.

2. Select Add Column > Column From Example > From Selection, and then type "January".

3. If you prefer the suggestion, press Enter and then select OK.

4. Power Query automatically renames the column to "month name".

5. Select the Order Date column, add a new column, enter "Day", and then select Monday from the list.

6. Power Query fills the column with day names and renames the column "Day Name".

7. To teach Power Query to recognize patterns, do the following:

   1. Select "Order Date column" and then add a new column.

   2. Enter "Monday, January 15^th, 2018", which corresponds with that row's date, and press Enter.
      
      If nothing happens, then copy the date you just entered and paste it in the row below. This time, Power Query has determined to do something with the Order Date, but not what.

   3. Press Enter, then go to the third row and enter "Saturday, January 20, 2018".

   4. Power Query fills in the date pattern, but the dates aren't quite correct, so let’s continue to teach the pattern.

   5. Double-click the fourth row and replace "January" with "February".

   6. Press Enter. Power Query figures out the pattern and applies it to the rest of the rows.

8. To return the transformed data to an Excel worksheet, select Home > Close & Load.

Note   If you still want to change the header row, name it to "Custom Date", for example.