Splitting multiple multi-valued cells in excel into rows

Question

I'm looking to split rows of multiple multi-valued cells into separate rows so that each of the row has one value from each of the multi-valued cells.

For instance if I have the following rows:

> Column1   Column2  Column3 
> 1         A:B:C    A1:B1:C1          
> 2         D:E:F    D1:E1:F1

I would want the result as following:

Column1   Column2  Column3
1         A        A1
1         B        B1
1         C        C1
2         D        D1
2         E        E1
2         F        F1

Is there a way in Power Query or VBA to split the data in the specificed format

Answer 1

Assuming you are starting off with source data in range Table1 that has three columns, and no column headers, this code will work in powerquery (paste into Home ... Advanced Editor...)

It creates two custom columns using the split function to split on : into a list

Then combines the two lists into a table, before we expand it

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Split([Column2],":")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Split([Column3],":")),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Table.FromColumns({[Custom],[Custom.1]})),
#"Expanded Custom.2" = Table.ExpandTableColumn(#"Added Custom2", "Custom.2", {"Column1", "Column2"}, {"Column1.1", "Column2.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.2",{"Column2", "Column3", "Custom", "Custom.1"})
in #"Removed Columns"

alternately, below works on any number of columns, all at once

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes (Source,List.Transform(Table.ColumnNames(Source), each {_, type text})),
TableTransform = Table.Combine(List.Transform(List.Transform(Table.ToRecords(#"Changed Type"), (x) => List.Transform(Record.ToList(x), each Text.Split(_,":"))), each Table.FromColumns(_, Table.ColumnNames(#"Changed Type")))),
#"Filled Down" = Table.FillDown(TableTransform,{"Column1"})
in #"Filled Down"