Forum Discussion

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
4 years ago
Solved

Split entries in multiple columns to rows

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.

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    4 years ago

    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

     

2 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    4 years ago

    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