Need to parse first 19 characters on the line in which a specific string is found

[bR851236574]

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?

[lbendlin]

it is not clear from your description if the data is all in one column or distributed across columns. Please provide a sample in table format

[Anonymous]

let we start with this attempt, assuming that the structure of the string is always the one provided:

= Table.AddColumn(#"Modificato tipo", "Personalizzato", each Text.BetweenDelimiters([comments],"#(lf)","-",{1, RelativePosition.FromEnd}))

In order to try a more general solution, You should eventually provide more examples with differente structure.

[Anonymous]

a more general-like approach to try to get the firsts 19 chars of the first (and only?) line containing a given string:

Table.AddColumn(#"Modificato tipo", "Personalizzato", each Text.Start(List.Select(Text.Split([comments],"#(lf)"), each Text.Contains(_,"(Add")){0},19))

[bR851236574]

Here are a few more examples of how the text is captured. Each color change is a different cell in the table.  I made the date/time I need bold.  As you can see, the order is variable.

[bR851236574]

Hopefully @lbendlin this is what you're asking for.

[lbendlin]

Thank you. That's a tough ask because you have to actually include the line feeds (\n) in the search.

In Power Query speak that is "#(lf)"

Here is an example.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjKNyTM2UYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.PositionOf([Column1],"#(lf)"))
in
    #"Added Custom"

[bR851236574]

@lbendlin I modified the query.  In the new custom column I created, it created a Table in the cell every time it found " (Additional comments)".  The problem is that is the only data created in new table is 25 34, 2.  Do I need to customize the query, if so which fields?

The field and string I'm looking for is ([Comments and Work notes],"(Additional comments)").  My table name is "ServiceNow Data"

if Text.Contains([Comments and Work notes],"(Additional comments)") then
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjKNyTM2UYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.PositionOf([Column1],"#(lf)"))
in
#"Added Custom" else ""

[lbendlin]

Yes, my example merely demonstrates how to locate the line feeds in your data. What you will need to change in your query is

- find the location of "(Additional comments)"

- find the location of the last "#(lf)"  before that location. If not found that means it's on the first line.

- grab the next 19 characters after that "#(lf)"  location (or the first 19 characters of your data if not found)

[lbendlin]

Here is another example that implements the logic.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY8/C8IwEEe/ytGpBaWX6x/sbToWOjk41A7BBhraJNAE+vWNKIrg4v7evd/1fYJ1TmVOSAiiYhRMAvbQSqvgbHSYIN3cOoN1QfnsaiMumhdeMNWM9MCdn6DTy6JWSI/jqIN2Vi5wc8YoG3yWDLuYqnI8RFc0IEoukKmIbifj+VYa5X+rsfnlUcXl2zu5TVlIL5+Jz9I/TyXDcAc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Comments and work notes" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "HasComments", each Text.PositionOf([Comments and work notes],"(Additional comments)")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "After LF", each try Text.PositionOf(Text.Middle([Comments and work notes],0,[HasComments]),"#(lf)",Occurrence.Last) otherwise -1),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Result", each if [After LF]>-1 then Text.Middle([Comments and work notes],[After LF]+1,19) else if [HasComments]>-1 then Text.Middle([Comments and work notes],0,19) else "no comments")
in
    #"Added Custom2"

Check it out in the Query editor.

[bR851236574]

Thanks for the additional example.  When I put it into my existing query, it created a sub-table which I was able to expand.  It created 3 rows for each existing row record in my existing table (ServieNow Data) and placed the same 3 results for each one so it doesn't look like it's actually looking to the field [Comments and Work notes] for each row but hardcoded the responses from the sample data I previously provided.

I wonder if this portion of the code is hardcoding something vs looking to the field.  I'm not sure what the random letter/numbers decode to.

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY8/C8IwEEe/ytGpBaWX6x/sbToWOjk41A7BBhraJNAE+vWNKIrg4v7evd/1fYJ1TmVOSAiiYhRMAvbQSqvgbHSYIN3cOoN1QfnsaiMumhdeMNWM9MCdn6DTy6JWSI/jqIN2Vi5wc8YoG3yWDLuYqnI8RFc0IEoukKmIbifj+VYa5X+rsfnlUcXl2zu5TVlIL5+Jz9I/TyXDcAc="

[Anonymous]

@bR851236574 

have you tried this expression?

Table.AddColumn(#"Modificato tipo", "Personalizzato", each Text.Start(List.Select(Text.Split([comments],"#(lf)"), each Text.Contains(_,"(Add")){0},19))

[Anonymous]

If you couls have more line with string "(Add ....",

try adding a custom column with ti code:

 List.Transform(List.Select(
        Text.Split([comments],"#(lf)"), 
              each Text.Contains(_,"(Add")),
                          each Text.Start(_,19) 
                                                 ))

[Anonymous]

@bR851236574 

Try this (with my toy example seems to do what you expect. You should carefully check the name of tables and columns involved and eventually change), where I appended my lines of code after the part of your script which load in the data:

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjKNyTM2UYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Dates_Comment", each
                                                List.Transform(List.Select(
                                                        Text.Split([Comments and Work notes],"#(lf)"), 
                                                                        each Text.Contains(_,"(Add")),
                                                                                   each Text.Start(_,19) 
                                                                                                         )),
    texp = Table.ExpandListColumn(extractString, "Dates_Comment")
in
    texp

[bR851236574]

Thanks @Anonymous.  I gave your custom column query below a try and am getting an EOF Error before I can file, but I'm not seeing a missed parentheses. Also do I need to edit the specific table name or field name somewhere in your example?   Thanks again.

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjKNyTM2UYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Dates_Comment", each
List.Transform(List.Select(Text.Split([Comments and Work notes],"#(lf)"),each Text.Contains(_,"(Add")),each Text.Start(_,19)texp = Table.ExpandListColumn(extractString, "Dates_Comment") in texp

[Anonymous]

Hi @bR851236574  some chars seem to have flown away 😀.

Try changing so that you have:

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjKNyTM2UYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Dates_Comment", each
List.Transform(List.Select(Text.Split([Comments and Work notes],"#(lf)"),each Text.Contains(_,"(Add")),each Text.Start(_,19) )),

texp = Table.ExpandListColumn(extractString, "Dates_Comment")

in texp

[bR851236574]

@Anonymous  I'm still getting the EOF error with your change. Also do I need to change any of these fields to match my table name or field names?

I also tried adding let Source = at the beginning to see if that would help.  It removed the EOF error but then received an expression error "The name "extractString" wasn't recognized.

[Anonymous]

copy and paste thefollowing  code in the add custom column tool in this way:

List.Transform(List.Select(Text.Split([Comments and Work Notes],"#(lf)"), each Text.Contains(_,"(Add")), each Text.Start(_,19)))

you get some table like this (I don't know all your column names)

after you've expanded the column named "Comments Date" you should get somethink like this:

[bR851236574]

@Anonymous 

Ok, I started from scratch to eliminate as many variables as possible.  The source data that I’ve imported only contains 1 column of data. 

Existing table name: #"ServiceNow Data"

Existing column in the table that contains the comments and work notes: [Comments and work notes]

I tried creating a custom column [Comments Date] once with each expression listed below.  Both returned Error in the new column.  Is there an applied step I’m missing while transforming the data?

Table.AddColumn(#"ServiceNow Data", "Comments Date", each List.Transform(List.Select(Text.Split([Comments and Work Notes],"#(lf)"), each Text.Contains(_,"(Add")), each Text.Start(_,19)))

List.Transform(List.Select(Text.Split([Comments and Work Notes],"#(lf)"), each Text.Contains(_,"(Add")), each Text.Start(_,19))

[Anonymous]

Hi,

inside custom column add, you should use ONLY the second form of espression, i.e. (*):

List.Transform(List.Select(Text.Split([Comments and Work Notes],"#(lf)"), each Text.Contains(_,"(Add")), each Text.Start(_,19))

pay attention to the names of the columns. Lowercase uppercase forms are also relevant.

Seems to me you used label "Comments and Work notes" to name a column, instead in the formual is used "Comments and Work Notes" ( first letter  of Notes is uppercase). Chenge one of both in order have the same name.

PS

If you get some error try to click on the cell (eventually drill-down) to get the complete description. Somjetime 😁 is usefull for debuging the code.

(*) the second expression is what you see inside advanced editor and is what PBI  construct for you when you input the first (shorter) form in the tool of add custom column

[bR851236574]

@Anonymous I had uppercase N and it was lower case.  Super helpful tips.  That seemed to work on my small test data!  I ran it on a larger data set an it errors when the [Comments and Work notes] field is null.

Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=[Type]

How do I modify the query to take null values into consideration?

I also expanded the testing and it looks like when there are multiple instances of Additional Comments it creates multiple lines.  I only need the date of the most recent Additional Comment.

For example if the cell contains the following it creates 7 rows, one for each Additional Comment.  I only need 06/19/2020 16:48:52

06/26/2020 09:54:59 - Ellie James (Work notes)
Weekly update: vendor may schedule a meeting next week.

06/19/2020 16:48:52 - Ellie James (Additional comments)
Made plansto meet with team.

06/12/2020 16:35:34 - Ellie James (Additional comments)
Weekly update: Awaiting a response from vendor about latest issue.

06/05/2020 15:15:08 - Ellie James (Additional comments)
Weekly update: Awaiting a response from vendor about latest issue.

05/29/2020 18:28:08 - Ellie James (Additional comments)
Weekly update: Awaiting a response from vendor about latest issue.

05/22/2020 18:09:50 - Ellie James (Additional comments)
Weekly update: Awaiting a response from vendor about latest issue.

05/15/2020 17:41:27 - Ellie James (Additional comments)
Weekly update: Awaiting a response from vendor about latest issue.

05/08/2020 08:55:27 - Ellie James (Additional comments)
Weekly update: Awaiting a response from vendor about latest issue...