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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors