Move lines from one column to another

[RonaldLieuw]

I have a file with Whatapps conversations and I want it split in two columns, either solution 1 or solution 2.
See image below.
I used split column with a delimiter and managed to split some rows.
The problem is that some rows don't have that delimiter.
How to transfer lines 4 & 5 from column A to B (solution 1) or better solution 2?
Thanks for the help.

 

[cyborgski]

Hi Ronald, below is just one of many ways to do this.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"App", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "App2", each (if Text.Start([App],1) <> "[" then "]" else "" ) & [App]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "App2", Splitter.SplitTextByDelimiter("]", QuoteStyle.Csv), {"App2.1", "App2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"App2.1", type text}, {"App2.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","",null,Replacer.ReplaceValue,{"App2.1"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"App2.1"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"App2.1"}, {{"Message", each Text.Combine([App2.2], "#(lf)"), type text}})
in
    #"Grouped Rows"

 

[RonaldLieuw]

Thank you very much Cyborgski, it works. I Don't know how but I will debug the M code.

 

[cyborgski]

Sorry Ron, I should have elaborated.

Add a column that tests to see if the row has a left bracket. If it does not, then we know we are missing a delimiter and will concatenate a delimiter to the front of the field. Now the split will force the text into the second column. The split will leave an empty string "" in the first split column. We replace the empty string with a null value. We can now fill down on the first split column. Finally, we can group by App2.1 and combine the text by the line feed delimiter Text.Combine([App2.2], "#(lf)") to yield the desired result.

Regards,
Mike

 

[RonaldLieuw]

Thanks Mike, I figured out on my self what you had done. Brilliant. Thanks again