Power Query: In the Zone

Welcome to our Power Query blog. This week, I look at some issues when dealing with extracted dates.

John, my reliable imaginary salesperson has provided some expense information. However, John was a bit confused by his recent trip to the US East Coast. John has decided that putting the dates in US format will be enough to sort out the time differences. The times he has entered are UK times.

Using ‘From Table’ on the ‘Get & Transform’ section on the ‘Data’ tab, I upload the data:

Since my locale is in the UK, the time has been converted to UK format (dd/mm/yyyy). I want to show the date in US format (mm/dd/yyyy).

The option to ‘Add Column from Examples’ is on the ‘Add Columns’ tab. I want to see how Power Query will convert my format:

Power Query is reorganising my date data into the correct format using M’s Text functionality.

However, my new date column is a text column, and it should be a date column. I convert the column datatype by right-clicking on the column.

This looks better, but I have another problem. The times are correct for the UK Date and Time, but not the US Date and Time. I need to correct the time on the US column. I could do this by simply subtracting five hours since I know that the US column is set to EST. However, I want to use the M function DateTime.AddZone():

DateTime.AddZone(datetime as nullable datetime, timezonehours as number, optional timezoneminutes as nullable number) as nullable datetimezone

This adds the timezonehours (and minutes if specified) as an offset to the input datetime value and returns a new datetimezone value.

I create a new column for the US date and time using ‘Custom Column’ option on the ‘Add Column’ tab.

I enter the M code: 

But the time is the same! I need one more step – I right-click on Correct US Date and Time, and convert it to a Date/Time column.

The column has now been converted to show the US time (which is five hours behind GMT).

Come back next time for more ways to use Power Query!