How to unpivot the multiple line from a cell into columns

[vinothkumar1990]

Hi,

I have two columns in a dataset called ID and Message. Message columns contains multiple lines with a cell. I want to unpviot them into columns. Is there anyway yo do?

Example,

Actual Data

ID	Message
1	M-1: Message1. M-2: Message2. M-3: Error. Message3.
2	M-1: Message1. M-2: Message2.
3	M-1: Message1.

What i need,

ID	Message	Messag1	Messag2	Messag3	Messag4
1	M-1: Message1. M-2: Message2.	M-1: Message1.	M-2: Message2.	M-3: Error. Message3.
2	M-1: Message1.	M-1: Message1.	M-2: Message2.
3	M-1: Message1.	M-1: Message1.

Thanks in advance!

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Message", "Message - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Message - Copy", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Message - Copy.1", "Message - Copy.2", "Message - Copy.3"})
in
    #"Split Column by Delimiter"

Hope this helps.