Help splitting string into new columns and rows by line feed delimiter

[DGiaco]

I have data on notes left in a source system automatically concatenated into a single column for each "job_id". 

Redacted esceprt below:

job_id	job_notes
1	10 Sep 2024 10:04 - (xxxxx) - P4 Standard
2	10 Sep 2024 10:14 - (xxxxx) - P5 Standard 10 Sep 2024 10:36 - (xxxxx) - PO Raised - xxxxx
3	10 Sep 2024 10:46 - (xxxxx) - P4 Standard 11 Sep 2024 10:34 - (xxxxx) - On site 11 Sep 2024 10:35 - (xxxxx) - Complete 11 Sep 2024 10:35 - (xxxxx) - Completion info supplied: xxxxx
4	10 Sep 2024 10:51 - (xxxxx) - P4 Standard 12 Sep 2024 10:53 - (xxxxx) - Completion info supplied: xxxxx

I need to be able to pull individual notes, so I am trying to split the job notes column to achieve the below example reult:

job_id	job_note_date	job_note_user	job_notes
1	10 Sep 2024 10:04	xxxxx	10 Sep 2024 10:04 - (xxxxx) - P4 Standard
2	10 Sep 2024 10:14	xxxxx	P5 Standard
2	10 Sep 2024 10:36	xxxxx	PO Raised - xxxxx
3	10 Sep 2024 10:46	xxxxx	P4 Standard
3	10 Sep 2024 10:46	xxxxx	On site
3	11 Sep 2024 10:35	xxxxx	Complete
3	11 Sep 2024 10:35	xxxxx	Completion info supplied: xxxxx
4	10 Sep 2024 10:51	xxxxx	P4 Standard
4	12 Sep 2024 10:53	xxxxx	Completion info supplied: xxxxx

My methodology was first to split into rows using each instance of a line feed as a delimiter, my thinking being the number of notes on each item will vary. From there it is a simple operation to split this column into separate columns for date/time, account and note contents.

I am stumped trying to account users naturally typing line feeds in their notes, for example in the below a user has pasted an email as a note in the source system, each line of the email and its signature have now been split into indiviudal rows.

Any support would be appreciated.

[v-xianjtan-msft]

Hi @DGiaco 

Here are my test results, hopefully they meet your requirements. Please let me know if it is not your expected output.

Try this in Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY/LCsIwEEV/ZchKoUKeXXTrB7TYZdpFIBECNQlNBT/fmoWYRoXOahjO5cyVEhFUIYKhNwEophwIbjCHExwerzmuW8ehX5TTatZorCSiZYJsEuKdGObBbWBW53ALF2Wj0euejknCSgmvf72VJCSX5B+1DqJdzDdQZODZ38JkdpDWO7Du6iHeQ5is0c1HC162EORvC5rDbJ9yfAI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [job_id = _t, job_notes = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"job_id", Int64.Type}, {"job_notes", type text}}),
    #"Split Notes" = Table.AddColumn(#"Changed Type", "Split Notes", each Lines.FromText([job_notes])),
    #"Expanded Notes" = Table.ExpandListColumn(#"Split Notes", "Split Notes"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Notes",{"job_notes"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Split Notes", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Split Notes.1", "Split Notes.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Split Notes.1", type datetime}, {"Split Notes.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Split Notes.2", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Split Notes.2.1", "Split Notes.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Split Notes.2.1", type text}, {"Split Notes.2.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Split Notes.1", "job_note_date"}, {"Split Notes.2.1", "job_note_user"}, {"Split Notes.2.2", "job_notes"}}),
    #"Removed Parentheses" = Table.TransformColumns(#"Renamed Columns", {"job_note_user", each Text.Remove(_, {"(", ")"})}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Removed Parentheses",{{"job_note_user", type text}})
in
    #"Changed Type3"

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DGiaco]

Thanks, 

This does achieve the correct format however it does not account for situations where a user has used a line feed in their comment on the source system so I am still getting the same null values as before, example below:

Is it even possible to account for these and achieve my desired result? 

[v-xianjtan-msft · Accepted solution]

Hi @DGiaco 

Adding a custom column assigns a value of 1 to rows containing null values and 0 otherwise, then filters out rows with a value of 1, removes the custom column, and then clicks Close and Apply.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY/NCsIwEIRfZclJoUJ+e+jVB6jYY5pDIBECNQ2mgo9vGkFIg6K7l2X5hpmREhHUIIJhsAEophwI7jCHA+we6+zTdeIwLNobfTNINRLRWkE2CvFWjH7dDc7aEu/hrF20Jt35mW1YbcPbT8GSBSktykS9h+gWW2OiwI7zNUz2Z87NHpy/zBDvIUzOmu6Vf/S5Aa8bCPKlAS1R9o8lUuoJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [job_id = _t, job_notes = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"job_id", Int64.Type}, {"job_notes", type text}}),
    #"Split Notes" = Table.AddColumn(#"Changed Type", "Split Notes", each Lines.FromText([job_notes])),
    #"Expanded Notes" = Table.ExpandListColumn(#"Split Notes", "Split Notes"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Notes",{"job_notes"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Split Notes", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Split Notes.1", "Split Notes.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Split Notes.1", type datetime}, {"Split Notes.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Split Notes.2", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Split Notes.2.1", "Split Notes.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Split Notes.2.1", type text}, {"Split Notes.2.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Split Notes.1", "job_note_date"}, {"Split Notes.2.1", "job_note_user"}, {"Split Notes.2.2", "job_notes"}}),
    #"Removed Parentheses" = Table.TransformColumns(#"Renamed Columns", {"job_note_user", each Text.Remove(_, {"(", ")"})}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Removed Parentheses",{{"job_note_user", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type3", "Custom", each if [job_note_date] = null or [job_note_user] = null or [job_notes] = null then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 0)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns1"

 Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[elitesmitpatel]

Please share the dummy PBIX file .