Mar 10 2022 04:26 AM
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.
Mar 10 2022 07:37 AM
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:
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