Date and Time in PowerQuery

As you may know, Standard Excel has lots of options for extracting date information from dates in Standard Excel. These include YEAR (extracts the Year from a date), DAY (returns the number of a day in a month from 1-31), MONTH (returns month number from 1-12), EOMONTH (gives the date of the last day of a month) and so on.  You can also use the TEXT function to extract things like the month name. However, even if you know that these functions exist, you often need to go rummaging around the internet to remind yourself of the function and what it does.

The great thing about PowerQuery is that it’s very easy to extract date or time information from dates in PowerQuery. We just have to click on a couple of buttons!

However before you can get to any of that exciting extraction, you need to convert your date columns into the ‘Date’ or ‘DateTime’ datatype. If it’s in a number of text format then you won’t be able to use the date extraction options, which we’ll go through in a moment.

Converting data to the ‘Date’ or ‘DateTime’ format is also useful as it means you’ll be able to filter the data column by date in PowerQuery. Once we eventually load the data into Excel, either into a table or a pivot table, you’ll also be able to filter the data by date in those tables as well.

Quickly though, what is the difference between ‘Date’ and ‘DateTime’?

 Well the ‘Date’ format shows the date only whereas the ‘DateTime’ datatype shows both the date and the time. How can you tell which one you can use?

Well a column of dates will be a column of 5 whole digits from 1-9 e.g 41539.  In Excel, each day is represented by 1, with the number 1 itself representing 1^st January 1900, number 2 being 2^nd January 1900, number 3 being 3^rd January 1900 and so on. The number 41539 is the date 22^nd September 2013 as it’s 41538 days since 1^st January 1900. Any such 5 digit serial number can be converted into the ‘Date’ data type. If you try and convert this information into a DateTime format then you’ll get a data formatting error as PowerQuery looks for some hour and minute information which it can’t find.

 A column of date and time information will be a column of 5 whole digits from 1-9 and then a decimal point and a fraction. As the number 1 represents a 24 hour period, 0.5 represents 12 hours, 0.25 represents 6 hours and so on. The number 41539.05 therefore represents 12 noon on 22^nd September 2013. A column of this type of number can be converted to DateTime datatype.

Converting a column to the ‘Date’ or ‘DateTime’ data type

So how can we convert a column to the ‘Date’ or ‘DateTime’ datatype?

• Select the column that you’d like to convert to the ‘Date’ or ‘DateTime’ data type,
• Go to the Home tab
• Select Data Type and then the ‘Date’ or ‘DateTime’ option.

Once we’ve done this, then we’ll see a new Changed Type action in our Query Settings listing.

Switching Locales.

Different countries arrange their dates in different ways.

For example, in the UK, the date is formatted dd/mm/yyyy. However in the USA, dates are formatted mm/dd/yyyy. When we receive files from systems that have different locale settings then this can sometimes trip us up in standard Excel. An example would be where a UK Finance Team with UK operating systems are using a file created in the US with a US operating system. In this example, if we don’t change the locale settings, then PowerQuery will create an Error.

To change the Locale Settings:

• Highlight the columns with Date in,
• Right click on the title of any of the highlighted columns,
• Select Change Type, and then Using Locale.
• Select Date, and then Change Locale. You should ensure that the Locale is in line with your operating system and then any Errors should be resolved

Date or DateTime information extraction introduction

As we said when we started off this lesson, it’s frequently very useful to extract the name of days of the week, name of months of the year or the number of days of the week or weeks of the year. This can be particularly useful when analysing data like sales trends or comparing retail weeks year on year. In Standard Excel, extracting all of that information is possible but you need to know the functions to be able to do it. In PowerQuery though we’ve got some simple buttons to extract this information. All we need to do is highlight the date column from which we want to extract the information and then select the extraction option that we want. However we’ll also lose the date information in the original column, so first we’d need to sort out duplicating the column.

How to Duplicate a Column.

Duplicating a column creates an exact replica of the duplicated column and puts it at the end of the dataset. The new column will have the same header but with the word ‘copy’ at the end of it.

1. Highlight the column that you’ like to duplicate
2. Right click the header of the column and select ‘Duplicate Column’.

Alternatively

1. Highlight the column,
2. Go to the Add column tab
3. Select Duplicate Column.

This then creates a Duplicated Column step in the Applied Steps pane of the Query Settings pane.

How to change the position of columns in the dataset.

Ok you’ve duplicated your column…but it’s at the end of your dataset so you’ll probably want to move that. Like in a pivot table, you can click and hold the header of the column you want to move and then drag it where you want it to go. That is a veeeeeeery slooooow way of doing things though. A possible faster plan is to:

1. Right click on the header of the column that you want to move
2. Select Move and then select Move to Beginning or Move to End. You can then drag the column where you want.

How to rename your column

Super simple this one. Double left click on the header that you want to rename and then when the header becomes highlighted, type in your new Header title.

Back to Date or DateTime information extraction

Date or DateTime information extraction introduction

Now you have a duplicated column in the right place in the dataset and with an appropriate Header. To extract our date information then:

1. Highlight the column formatted as a date from which you’d like to extract your data
2. Go to the Transform tab,
3. in the Date/time section of the ribbon, select Date.
4. There are a lot of different options here, so take some time to have a play around, but here’s a rundown of what each does:

Age – Calculates the length of time between the current date and the date in each row of the column. Perfect for calculating things like debtor ageing.

Date Only – For those columns formatted as a DateTime, this option removes the Time element

Year, Month, Quarter, Week and Day – each have subsequent options such as Start of Year, End of Year or Start of Month, End of Month, which give the first or last date of the relevant time period. You can also extract from here the number of the day of the week from 1-7, or the name of the week, the number of the month from 1-12 and so on.

Earliest and Latest – This returns a table showing the Earliest and Latest dates in the column.

To extract the name of the month from the date, select your date column then select the ‘Year’ option and then the ‘Name of Month’ option. You’ll then have a column with the name of the month :-D.