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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/07-jul/86/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/07-jul/86/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/07-jul/86/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/07-jul/86/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
The M formula I have used is
= Date.FromText([First Characters])
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/07-jul/86/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/07-jul/86/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/07-jul/86/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
The formula I have used is
= Date.FromText([First Characters], "en-US")
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/07-jul/86/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/07-jul/86/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/07-jul/86/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
Now I will use a shorter form.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/07-jul/86/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
The M formula I have used is
= Date.ToText([Correct Expense Date], "ddd", "en-US")
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/07-jul/86/image12.png/a1537847463e660a31158c8032525438.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/07-jul/86/image13.png/917da985be13220165c8d2823e95344f.jpg)
I add a new column where the number is converted to a date.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/07-jul/86/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
The M formula I have used is
=Date.From([Date as Number])
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/07-jul/86/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/07-jul/86/image16.png/d082e3477129350b8a2a589156028e63.jpg)
The M formula I have used is
= Date.ToRecord([Correct Expense Date])
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/07-jul/86/image17.png/a468c9c28195e8f18fdedfa59a0cf042.jpg)
I can see the records and I can see what is in them by expanding the Data Record column:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/07-jul/86/image18.png/61935f470d8b9f21b7b7683ffca40895.jpg)
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!