Split Column into Rows

[jabrillo]

I have one column with the following data:

Ref	Data
1	20200101 - Juan Perez The quick brown fox 20200102 - Elton Hamilton Jumped over the lazy dog
2	20200103 - Jacqueline Seymour When the sun is up 20200104 - Jerry Seacrest And everything is okay

I want to split the second column into three columns and rows depending on the number of line breaks. It should look like this:

Ref	Date	Name	Comment
1	20200101	Juan Perez	The quick brown fox
1	20200102	Elton Hamilton	Jumped over the lazy dog
2	20200103	Jacqueline Seymour	When the sun is up
2	20200104	Jerry Seacrest	And everything is okay

How will I do this in Power Query? Thank you...

[AlexisOlson · Accepted solution]

You should be able to split the column into new rows using a double line feed and then split again into columns using a single line feed and then once more on " - ".

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY2xDoIwFAB/5aUzJqX6Aw4mhslEEwfKUOEJDfAKpVXL19saE91uuMuVJctZxgQXnOc8hw0UXhGc0OIq6dIhzF7XPdyseRLczUuSpK8ton0YnCE4qlEnkFT4ccIGzAMtuFgPag3QmJZVWcnE77RNJ1XPHgdNCGcMo/FW0rVD+oSLJ9AL+OlvuEsRWhuir2qLi5O0pwYw3oLrNLUpMb0KrKre", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ref = _t, Data = _t]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Data", Splitter.SplitTextByDelimiter("#(lf)#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Data"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Data", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Date-Name", "Comment"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Date-Name", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Date", "Name"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Ref", Int64.Type}, {"Date", Int64.Type}, {"Name", type text}, {"Comment", type text}})
in
    #"Changed Type"

[smpa01]

@jabrillo  question 

does your string look like 

20200101 - Juan PerezThe quick brown fox 20200102 - Elton HamiltonJumped over the lazy dog

or

20200101 - Juan Perez The quick brown fox 20200102 - Elton Hamilton Jumped over the lazy dog