Power Query: Technically Dating
25 July 2018
Welcome to our Power Query blog. This week, I look at some useful logical M functions to convert data into dates or vice versa.
To conclude my series on dates, I will take a look at some functions that I can use to convert a date into a different datatype. For example, one such datatype would be text, so that I may use a date as part of a reference. I will also look at functions that perform the opposite process, converting dates into text.
Date.FromText
Date.FromText(date as nullable text, optional culture as nullable text) as nullable date
Returns a date value from text (date) in a recognised date format according to the culture, following the ISO 8601 format standard.
It’s probably helpful to know what formats would be accepted here. For en-US culture, then text in the form yyyy-MM-dd is accepted, as is yyyyMMdd and M/d/yyyy. Cultures such as the UK and Australia prefer to put the day before the month, so it helps to know the windows culture being used: this may be specified in any of the formulas I am looking at today.
I have some data from my fictional sales person, John. He has decided to embed the date in a column and I need to extract it and convert it into datatype date so that I can link the data to other tables.
The current Expense Type is a text field that contains the date. I want to extract the date part of the text column and convert it to a date. I begin by extracting the date using the ‘Extract’ option in the ‘From Text’ section on the ‘Add Column’ tab.
I could now just convert this to a date by using the transform menu, but instead I will enter the formula directly as I create a new custom column.
The M formula I have used is
= Date.FromText([First Characters])
My date has been converted correctly. But why would I ever need to use a formula instead of just using transform? Suppose I had the next dataset coming in.
Well that doesn’t look good. I need to alter my Expense Date formula. I will add a new column that will tell Power Query to use en-US culture, as John has clearly used US date formatting.
The formula I have used is
= Date.FromText([First Characters], "en-US")
The date has been calculated correctly because I have specified the culture used.
Date.ToText
Date.ToText(date as nullable date, optional format as nullable text, optionalculture as nullable text) as nullable text
Returns a textual representation of date. This function takes in an optional format parameter.
The full list of formats that are supported are supposedly in the Microsoft help pages, but Lord Lucan is a little easier to find; I’ll give a couple of examples in the exercise that follows.
I can use this M function to format dates ready to add them to a reference or to display them in a required format for a report. I will format Correct Expense Date in a couple of ways to show how this function can be used.
The M formula I have used is
= Date.ToText([Correct Expense Date], ddd-MMM-yyyy", "en-US")
I have to mention the culture because the date is held using the US date format.
Now I will use a shorter form.
The M formula I have used is
= Date.ToText([Correct Expense Date], "ddd", "en-US")
This gives an idea of the range of different texts that may be produced.
Date.From
Date.From(value as any, optional culture as nullable text) as nullable date
Returns a date value from a value.
Similar to Date.FromText(), the main advantage that this function has over the standard menu option of transforming a number to a date, is that I can specify the culture. Below, a column has been inserted that contains the expense date in a numeric format:
I add a new column where the number is converted to a date.
The M formula I have used is
=Date.From([Date as Number])
The date is shown in its proper format for my system.
Date.ToRecord
Date.ToRecord(date as date) as record
Returns a record containing parts of a date value.
This is more for the techies amongst us, as it will create a record containing my year, month and day as entries. I will do this for Correct Expense Date.
The M formula I have used is
= Date.ToRecord([Correct Expense Date])
I can see the records and I can see what is in them by expanding the Data Record column:
This is a nice way to extract the year, month and day in one move.
Come back next time for more ways to use Power Query!
