cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vinothkumar1990
Helper II
Helper II

How to unpivot the multiple line from a cell into columns

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

IDMessage
1M-1: Message1.
M-2: Message2.
M-3: Error. Message3.
2M-1: Message1.
M-2: Message2.
3M-1: Message1.

 

 

What i need,

 

IDMessageMessag1Messag2Messag3Messag4
1M-1: Message1.
M-2: Message2.
M-1: Message1.M-2: Message2.M-3: Error. Message3. 
2M-1: Message1.M-1: Message1.M-2: Message2.  
3M-1: Message1.M-1: Message1.  

 

 


Thanks in advance!

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

1 REPLY 1
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors