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 

Screenshot.png

With Table1 in Excel workbook:

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

corresponding sample attached