Need help in separating line feed data

Occasional Visitor

Hi Guys,


I have encountered a problem while working on MS excel. So, I have data in multiple lines (alt + enter) in consecutive columns. Now, I have tried to use the power query delimiter tool to separate these values into rows. But that has really increased the number of entries. 


What I want is that the line feed data in each column should correspond to the line feed data in the other column. Any suggestions would be appreciated. 

1 Reply

Hi @Shubham_22 


If you always have the same number of Line Feed in each column (as in Table1 below) that's fairly easy 


With Table1 in Excel workbook:

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ToColumns = Table.ToColumns(Source),
    Transformed = List.Transform(ToColumns, each
            List.Transform(_, each Text.Split(_,"#(lf)"))
    ToTable = Table.FromColumns(Transformed)

corresponding sample attached