Please note javascript is required for full website functionality.
MVP

Blog

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 dateThis 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!

Newsletter