Split entries in multiple columns to rows

[Ashish_Mathur]

Hi,

I have data in an MS Excel worksheet which has 13 columns and 2 rows.  Of the 13 columns, there is a single entry in the first 4 columns and in the balance 9 columns, each cell has multiple entries seperated by , and Alt+Enter.  I'd like to split data into rows so that each entry in the 9 columns appears in a rows of its own.

Please find my data and expected result in this workbook.

Thank you for your help.

[Ashish_Mathur]

Thank you @PhilipTreacy 

[PhilipTreacy · Accepted solution]

Hi @Ashish_Mathur 

Download Excel workbook example

Transpose the table, split the columns by delimiter (comma), transpose the table again, then fill down for the relevant columns.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column2.1", "Column2.2"}),
    #"Transposed Table1" = Table.Transpose(#"Split Column by Delimiter1"),
    #"Filled Down" = Table.FillDown(#"Transposed Table1",{"Column1", "Column2", "Column3", "Column4"})
in
    #"Filled Down"

regards

Phil