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 need the first 19 characters of the line in which the first string of " (Additional comments)" is found.
The cell contains text with potentially multiple entries of work notes and/or additional comments with their associated date/time stamp. I only want the most recent date/time for additional comments.
Here's an example of one of the cells from my source data. The way the data is stored, the most recent entry is always first. As you can see the work notes are most recent. I need it to return "06/19/2020 13:26:02".
Data Example:
06/24/2020 15:01:21 - Jane Smith (Work notes)
Adding: Build ECheck-in WQ for ASC
06/19/2020 13:26:02 - Josh Miller (Additional comments)
Tina Jones will add to the Front Desk Report
Attempts:
if Text.Contains([Comments and Work notes],"(Additional comments)") then Text.BeforeDelimiter([Comments and Work notes]," -") else ""
This works if an additional comment is most recent but returns 06/24/2020 15:01:21 when I need 06/19/2020 13:26:02.
if Text.Contains([Comments and Work notes],"(Additional comments)") then Text.BeforeDelimiter([Comments and Work notes]," (Additional comments)") else ""
Again, this works if additional comments are the most recent, otherwise it returns:
06/24/2020 15:01:21 - Jane Smith (Work notes)
Adding: Build ECheck-in WQ for ASC
06/19/2020 13:26:02 - Josh Miller
Unfortunately I can't capture x number of characters to the left of " (Additional comments)" as a delimiter because the name right before it varies in length and it's not in between 2 delimiters.
Any thoughts or suggestions?
Solved! Go to Solution.
I hadn't read everything and I hadn't seen the case of many lines with "(Add ...".
the modified expression to handle this situation as required:
List.Max(List.Transform(
List.Select(try Text.Split([comments],"#(lf)") otherwise {},
each Text.Contains(_,"(Add")), each DateTime.FromText(Text.Start(_,19),"en-US"))))
I had to use the optional "en-US" parameter because the dates are in a different format from my country.
You probably don't need it.
try wrapping text.split in try otherwise:
List.Transform(List.Select(try Text.Split([comments],"#(lf)") otherwise {}, each Text.Contains(_,"(Add")), each Text.Start(_,19)))
and good luck
I hadn't read everything and I hadn't seen the case of many lines with "(Add ...".
the modified expression to handle this situation as required:
List.Max(List.Transform(
List.Select(try Text.Split([comments],"#(lf)") otherwise {},
each Text.Contains(_,"(Add")), each DateTime.FromText(Text.Start(_,19),"en-US"))))
I had to use the optional "en-US" parameter because the dates are in a different format from my country.
You probably don't need it.
@Anonymous That did it. You're awesome! Thank you so much.
@Anonymous Awesome, that fixed the null, did you see the issue I added with multiple additional comments?
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.