PowerQuery (M): How can I extract a date from a large text field?

Question

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.

MarcelBeug MarcelBeug · Accepted Answer · 2017-05-11T17:50:25

I guess you mean the Power Query Text functions. These are somewhat limited indeed, but there are plenty other options in Power Query's function library: in this case the List functions can come to the rescue.

By the way: I checked for " NFU" in order to avoid "CONFUSION" (last but one line in your examples).

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"example", type text}}),
    LastNFU = Table.AddColumn(Typed, "LastNFU", each Text.PositionOf([example]," NFU",Occurrence.Last), Int64.Type),
    AfterNFU = Table.AddColumn(LastNFU, "AfterNFU", each if [LastNFU] = -1 then null else Text.Range([example],[LastNFU]+4)),
    Trimmed = Table.TransformColumns(AfterNFU,{{"AfterNFU", Text.Trim}}),
    TextToList = Table.TransformColumns(Trimmed,{{"AfterNFU", each if _ = null then {} else Text.ToList(_)}}),
    ListFirstN = Table.TransformColumns(TextToList,{{"AfterNFU", each List.FirstN(_, each Text.Contains("01234567890-/",_))}}),
    TextCombine = Table.TransformColumns(ListFirstN, {"AfterNFU", Text.Combine, type text}),
    Date = Table.TransformColumnTypes(TextCombine,{{"AfterNFU", type date}}, "en-US"),
    Renamed = Table.RenameColumns(Date,{{"AfterNFU", "Date"}}),
    Removed = Table.RemoveColumns(Renamed,{"LastNFU"})
in
    Removed

PowerQuery (M): How can I extract a date from a large text field?

2 Answers