Extract Date Time From Texts In Several Scenarios

[v-alq-msft]

Scenario 1: 
Some organizations save date time together with other details recorded in text type that cannot change the field type to Date/Time. For example,

Requirements

How can we transfer the text field into date/time format to be used in later calculation? The most straight forward and commonly used ways is to remove the end strings that is not date/time, we want to remove the weekdays here.

Power Query Method

Using M code is pretty similar the DAX, we have Text.Start() Returns the first count characters of text as a text value, and Text.Length to return the length.

We can tell from this example, that LEFT() in dax is matched with Text.Start(), and LEN() is similar to Text.Length. In addition, if the weekday is recorded in the start strings e.g. “Mon 2019 Dec 26 10:20:04”, we could replace the formulas with RIGHT() and Text.End() respectively.

Dax Method:

The LEFT() is used to return specified number of characters from the start of a text string, and LEN() returns the number of characters in a text string. As the result, the formulas removes the last 4 stings returns only date and time information, so we can just change the data type to date/time.

Scenario 2: 

Suppose date time is recorded with others texts (e.g. names, products) when the text string length differs, in this case we cannot use the above method.

                                            

Power Query Method

The quick method is using split feature in Query Editor, split by the right most delimiter “ “ in this example, and then remove the end text column after splits.

If the text is at the beginning, to remove it we should select the left most delimiter.

DAX Method:

DAX method requires a dax formula compared with Power Query solution, also LEN() and SUBSTITUTE() would be used.

Text at the end:

Date/time = LEFT([DateTimeField],FIND("-",SUBSTITUTE([DateTimeField]," ","-",LEN([DateTimeField])-LEN(SUBSTITUTE([DateTimeField]," ",""))))-1)

Text at the beginning:

Date/time = RIGHT([DateTimeField],LEN([DateTimeField])-SEARCH("-",SUBSTITUTE([DateTimeField]," ","-")))

Scenario 3: 

Suppose there is more information recorded and saved together with date/time in a mixed order. How can we deal with this field? The requirement is basically take out the date time information and remove the rest redundant texts.

Power Query Method:

The problem is there are other details recorded together in the field that is neither at the beginning or end. What we can do is use Split() function to split the column by the blanks in the cells, and track the year, month, day and time based on the split order.

To achieve this, we can create a custom column. The formula splits the column by “ ” (blank), and create a datetime using DateTime.From Text() function by taking out the split parts with given order.

= Table.AddColumn(#"Changed Type", "DateTime", each

let split = Text.Split( [DateTimeField], " " ),

            dateTime = DateTime.FromText( split{1} & " " & split{2} & " " & split{3} & " " & split{4} )

        in  dateTime)

In this sample, number 0 is the text sting before the first blank, and 1 is the year, 2 is the month, and so on. If the cell recorded in other format like “2020 XXX 12:00:00 19 Oct”, the number in the split{} should be changed accordingly.

Of course, we would need to change it to date/time type afterwards. Once we have this datetime column, we should able to use it as a general datetime field for later calculations e.g.:(weekday, YearMonth,etc)

Weekday: = Table.AddColumn(#"Changed Type1", "Weekday", each Date.DayOfWeekName([DateTime], "en-us"))

YearMonth = Table.AddColumn(#"Added Custom1", "YearMonth", each Text.Combine({DateTime.ToText([DateTime], "yyyy"), "-", DateTime.ToText([DateTime], "%M")}), type text)

DAX Method

Using DAX in this scenario is rather complex, we could split the column by using PATHEITEM (). The function is used here to return the item at the specified position from a string resulting from evaluation of a PATH function. Positions are counted from left to right. Then we can use CONCATENATE to combine the parts together.

DAX datetime =

var p1 = PATHITEM( SUBSTITUTE( [DateTimeField], " ", "|" ) , 1)

var p2 = PATHITEM( SUBSTITUTE( [DateTimeField], " ", "|" ) , 2)

var p3 = PATHITEM( SUBSTITUTE( [DateTimeField], " ", "|" ) , 3)

var p4 = PATHITEM( SUBSTITUTE( [DateTimeField], " ", "|" ) , 4)

var p5 = PATHITEM( SUBSTITUTE( [DateTimeField], " ", "|" ) , 5)

Return    CONCATENATE(p2,CONCATENATE(" ",CONCATENATE(p3, CONCATENATE(" ",CONCATENATE(p4,CONCATENATE(" ",p5))))))

 

This is how one can extract date time from texts in several scenarios. Hope this article helps everyone with similar questions here. 

Author: Paul Zheng

Reviewer: Yu Yang

Dealing with “Text Type” DateTime Field.pbix