cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mo-PHAT Frequent Visitor
Frequent Visitor

Split a column of text when a date/time occurs

I have a column with multiple work notes in it separated by date/time stamps.

 

How do I split this column using the date/time stamps as the delimiter?  IS there a wildcard for numbers?

 

I cannot use Carriage return or Line feed as some work notes have much longer entries with CR's and LF's in them.

 

For example:

 

"13-05-2019 22:49:51 - Staff Name (Work notes)
Blah Blah

Blah Blah

13-05-2019 21:00:01 - Staff Name (Work notes)
Blah Blah Blah

 

If I could use a date as a delimiter like DD-MM-YYYY HH:MMSmiley FrustratedS it would be of great help.

8 REPLIES 8
Super User
Super User

Re: Split a column of text when a date/time occurs

@Mo-PHAT does notes always starts wiht date/time or it could be anywhere in the text.





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




Highlighted
Mo-PHAT Frequent Visitor
Frequent Visitor

Re: Split a column of text when a date/time occurs

Hi,

 

It always starts with a DateTime but could be any staff name and any amount of characters to the next note beginning with a DateTime.

Super User
Super User

Re: Split a column of text when a date/time occurs

@Mo-PHAT go to edit queries, add custom column with following expression

 

Text.BeforeDelimiter([Notes], " ", 1)

and change the data type to date time





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




Mo-PHAT Frequent Visitor
Frequent Visitor

Re: Split a column of text when a date/time occurs

That gets the first date time out. 

 

Sorry, I may not have explained properly.  I want to use the DateTime to split the column as I only want to return the very first Note entry from each cell.

Super User
Super User

Re: Split a column of text when a date/time occurs

@Mo-PHAT not fully sure what you mean? Can you provide example?





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




Mo-PHAT Frequent Visitor
Frequent Visitor

Re: Split a column of text when a date/time occurs

So originally one cell would be:

 

12-05-2019 14:17:56 - Staff Name (Work notes)
Check-in complete

Called Customer

12-05-2019 09:00:07 - System (Work notes)
This ticket was automatically generated by the system.

12-05-2019 10:00:07 - Staff Name (Work notes)
Investigating.

 

And I only want to return:

12-05-2019 14:17:56 - Staff Name (Work notes)
Check-in complete

Called Customer

Super User
Super User

Re: Split a column of text when a date/time occurs

@Mo-PHAT still not very clear, does the following text part of one record, do you want text between first date and before 2nd date? So confusing what you are actually looking for

 

12-05-2019 14:17:56 - Staff Name (Work notes)
Check-in complete

Called Customer

12-05-2019 09:00:07 - System (Work notes)
This ticket was automatically generated by the system.

12-05-2019 10:00:07 - Staff Name (Work notes)
Investigating.





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




Mo-PHAT Frequent Visitor
Frequent Visitor

Re: Split a column of text when a date/time occurs

Yes, all of that text is in one cell basically a concatenation of all of the work notes for a ticket.

I want to extract only the top (latest) work note.

So from the example, I would only want to return this note:

 

12-05-2019 14:17:56 - Staff Name (Work notes)
Check-in complete

Called Customer