Extract Latest Date from a text field with multiple timestamps

[EricH77]

Hi,

I am looking for a way to extract the latest timestamp from a text column that that contains  multiple notes and timestamps.  The ultimate goal is to be able to identify which projects have been updated in a specific month based on the most recent timestamp.  An example of the column data is presented below:

Column Name is Quantity Completed Field History

Example Data:

2023-05-24 9:44:50: Mike changed quantity completed from 0 to 2 2023-06-27 5:48:49 Mike changed quantity completed from 2 to 4 2023-07-04 9:22:48 Namoi changed quanitity completed from 4 to 5

I tried creating a column from just the first 20 characters extracted but that only works for projects with a single update.  Once they have multiple updates, I'm not sure how to only capture the latest timestamp since it gets added to the end of the existing field and can be different character lengths from the end depending on the persons name that is updating the field.

Thanks for the help!

[rubayatyasmin · Accepted solution]

Hi, @EricH77 

1. Create a new column that splits your text at each space character. This can be achieved using the 'Split Column' functionality in Power Query. Choose 'By Delimiter' and input a space " " as the delimiter. Choose to split at each occurrence of the delimiter. This will result in a lot of new columns, one for each word in your original text.

2: Create a new column that uses the Text.Contains function to check if each split part of the text is in a date format. This can be done by using Custom Column under the Add Column tab, and the formula could be:

if Text.Length([ColumnName]) = 19 and Text.At([ColumnName], 4) = "-" and Text.At([ColumnName], 7) = "-" and Text.At([ColumnName], 10) = " " and Text.At([ColumnName], 13) = ":" and Text.At([ColumnName], 16) = ":" then [ColumnName] else null

Replace "ColumnName" with the name of the column from step 1. This will create a new column with nulls and dates.

3: Filter out the nulls in this new column.

4: This step is to find the maximum date in the list. Under the Transform tab, click on "Group By". A new window will pop up. In the new window, leave the default setting at "All Rows". Add a new grouping, name it something like "MaxDate", and choose the operation to be "Max". For the column, choose the column you created in step 2.

5: Click "OK". Now you should have a new column that contains the most recent timestamp for each row of data.

new column should have the latest timestamp.