My table has a text column called Remarks
which usually contains a large amount of text.
Here's an example:
3/24/2017 11:14:41 AM - EMD FOR STATUS NFU 3/30/17
3/30/2017 10:58:03 AM - CLD PER RECEPTIONIST GM UNAVAILABLE NFU 04-13-2017
4/13/2017 11:10:15 AM - CLD PER RECEPTIONIST WILL GIVE INFO NFU4/27
4/27/2017 9:02:20 AM - MLD INV WITH 90 DAY STAMP
4/27/2017 9:15:03 AM - PER REP WILL CALL CUSTOMER FOR PAYMENT
4/27/2017 11:03:46 AM - NFU 05/5PER REP CUSTOMER CONFUSION
5/5/2017 8:55:17 AM - NFU 5/9/2017 CRP PER REP CHECK WAS MLD 5/2/17
All of that text would be crammed into a single field, and I need to extract the last NFU date from the field for use in calculations and filtering.
In the above example, I would want to extract the date 5/9/2017 from the last row.
But as you can see, the date could be in any format, anywhere in the field.
I presume Excel can parse the text into a date value in any of the above formats (if not, I'll deal with that some other way - employee training, etc.)
The main things I need to figure out how to do using PowerQuery are:
- Find the last instance of "NFU" in this field
- Extract all text immediately following that last instance of "NFU", including the space between "NFU" and the date, if present.
At this point, the result should be:" 5/9/2017 CRP PER REP CHECK WAS MLD 5/2/17"
- Remove any whitepsace at the beginning of the string.
At this point, the result should be:"5/9/2017 CRP PER REP CHECK WAS MLD 5/2/17"
- Find the first character that is not
0-9
,/
, or-
(or the end of the string, whichever comes first) - Truncate the string at the first non-date character, if appropriate.
At this point, the result should be:"5/9/2017"
- Finally, attempt to format the resulting text into
Date
type/format, and return as the result for a PowerQuery custom column.
Looking at the PowerQuery string functions available, I'm not sure whether this is even possible.