NOTE: This post was created using the November 2014 version of Power Query. In later versions, some of the screens or functionality could be different.

You can download the Excel 2013 file necessary to perform the steps in this post here. To get to the existing query, click the Workbook Queries button on the Power Query ribbon.

In this post, we will play with transformation options available for Date typed columns. Our starting point is shown in Figure 1.

NOTE: Because my regional settings for Windows are set to English – United States, my default format for dates is mm/dd/yyyy. So, in this post, my dates will follow that format. The functions in Power Query will work appropriately for your region.

Figure 1

The Birth Date column still has the Any data type. Our first course of action will be to change its type to Date.

1. With the Birth Date column highlighted, click the Data Type option on the ribbon (on either the Home or Transform tabs). This will reveal the options shown in Figure 2.

Figure 2

2. Choose Date. The result will resemble Figure 3.

Figure 3

Note that the values in the column have become right-justified, are now italic, and the hyphens between the date parts have changed to slashes. Also, you will notice that the final query step in the APPLIED STEPS section is still Changed Type1. Since the last step performed in the last post was to change the data type of the Award Percentage column to a decimal number, and the first of this one is to change the type of the Birth Date column to a Date, Power Query merged both of those changes into a single step.

Since we will want to keep the Birth Date column while we play with the various options, we will be adding new columns. New columns are added to the far right of the query. So, our next course of action will be to move the Birth Date column to the end to make it easier to view it alongside the new columns.

3. Right-click the Birth Date column to expose the menu shown in Figure 4. Note, if you are using a device with no Right-click option, you can also use the Move button on the Home tab of the ribbon.

Figure 4

4. Choose “To End” as shown in Figure 4. The result will resemble Figure 5.

Figure 5

The Birth Date column is now at the far right of the query, after Award Percentage.

5. With the Birth Date column selected, click the Date button in the From Date & Time section of the Add Column tab of the ribbon, as shown in Figure 5. This will reveal the options shown in Figure 6.

Note: It is important to use the Add Column tab here so that our transformation will create a new column instead of replacing the existing one.

Figure 6

As dates have multiple parts, there are separate functions you can use for dealing with each part.

Let’s create a new column that returns the Month part of the date.

6. From the Month menu shown in Figure 6, choose Month, shown by the arrow in Figure 7.

Figure 7

The result will be a new column, called Month by default, to the right of the Birth Date column, as shown in Figure 8.

Figure 8

Note that this returns the ordinal number for the month, not a name. In Figure 8, I am showing the Home tab in order to highlight the fact that the Month transformation also performed the added work of converting the resulting column to the Decimal Number data type.

7. Since we will not be keeping this column, click the X next to the Inserted Month query step, as shown in Figure 9.

Figure 9

This will take our query back to the way it was in Figure 6, shown again below as Figure 10.

Figure 10

In addition to finding the parts of a date, you can also find other values based on that date. For example, you can use a date value to find the last day of that month. This could be useful in situations where the number of days in a month will matter. This functionality is easier than building logic to determine which years were leap years (ah, February) and then building the End Date column from there.

8. From the Date menu on the Add Column tab, choose Month –> End of Month, as shown in Figure 11.

Figure 11

The result is shown in Figure 12.

Figure 12

Note that the transformation also correctly typed the resulting column as a Date.

We do not want to keep this column, so we will delete this step.

9. Click the X next to the Inserted End of Month step in the APPLIED STEPS, as shown by the arrow in Figure 13, to delete it.

Figure 13

This will, once again, take us back to the state of the query in Figure 6, as shown in Figure 14.

Figure 14

I used this dataset for a demo in which I wrote the result to the Excel Data Model (we will cover that in a future post). One thing I had not though about was that the earliest value supported for a Date column in Power Pivot is January 1, 1900. You can see from the data that there are many Nobel Laureates who made the the inconvenient decision to be born prior to 1900. The nerve of some people… 🙂 For that reason, I determined it was best to just use the Birth Year of each Laureate instead of the full date. To clarify, we want to replace the Birth Date column with a Birth Year column.

10. With the Birth Date column selected, click the Date button in the Date & Time Column section of the Transform tab of the Ribbon, as shown in Figure 14. This will reveal the familiar Date options shown in Figure 15.

Figure 15

11. Choose Year from the Year option. This will produce the result shown in Figure 16.

Figure 16

Figure 16 shows the Home tab in order to highlight the Data Type.

Since we used the Transform tab instead of the Add Column tab, the existing Birth Date column containing the full date was replaced with a Birth Date column containing just the Year part of the Birth Data. Note that the data type was changed to a Decimal Number. We have a few issues with this result. First, the name of the column no longer makes sense for the content it holds. And second, we will never ever want to perform math on these values, so a text data type would be more appropriate in order to prevent mathematical operations that have no hope of being valid.

12. On the Home or Transform tabs, change the data type of the Birth Date column to Text, as shown in Figure 17.

Figure 17

Notice that the values in the column are now left-justified.

13. Right-click the Birth Date column header, and choose Rename, as shown in Figure 18.

Figure 18

Note that you can also double-click the column heading or use the Rename button in the Manage Columns section of the Home tab to rename this column.

14. Name the column Birth Year. The result should resemble Figure 19.

Figure 19

If you want to save this query and file for use in the next post, go for it. If you do, you should notice that there are two errors found, as shown in Figure 20.

Figure 20

Those will be dealt with in the next post, which covers handling errors.