Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
EricH77
Frequent Visitor

Extract Latest Date from a text field with multiple timestamps

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!

1 ACCEPTED SOLUTION
rubayatyasmin
Super User
Super User

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. 

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


View solution in original post

1 REPLY 1
rubayatyasmin
Super User
Super User

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. 

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors