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.
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:MM:SS it would be of great help.
@Mo-PHAT does notes always starts wiht date/time or it could be anywhere in the text.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
@Mo-PHAT go to edit queries, add custom column with following expression
Text.BeforeDelimiter([Notes], " ", 1)
and change the data type to date time
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
@Mo-PHAT not fully sure what you mean? Can you provide example?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |