Forum Discussion
Help splitting string into new columns and rows by line feed delimiter
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: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.
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.
4 Replies
- elitesmitpatel
Solution Supplier
Please share the dummy PBIX file .
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.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?
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.
