Forum Discussion
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 ent...
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
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