Parent Split for two columns

[jomaor1]

Hello guys, I have a very simple problem, but I cannot seem to find a solution.

I have a very simple table containing 3 columns:

Product, Serial & Heritage as below image.

Unfortunately for the Serial & Heritage numbers my input table comes all in the same cell, so I need to split this into new rows.

Basically the result table (3rd in the image) needs to have the amount of rows linked to the quantity of Serials/Heritage.

Note that the quantity/order of Serial & Heritage are always equal/the same.

Any ideas?I tried to input Index columns to help me think of something, but nothing helped.

[dufoq3]

Hi @jomaor1, another solution:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQJc1bSUXLUcdIB0YY6RjrGSrE60Uohjn7eQAEXHVcdNyBtomOqY6YUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Serie = _t, Patri = _t]),
    Transformed = List.TransformMany(Table.ToRows(Source),
        each List.Zip(List.Transform(List.Skip(_), (x)=> Text.Split(x, ","))),
        (x,y)=> Table.FromRows({{x{0}} & y}) ),
    Combined = Value.ReplaceType(Table.Combine(Transformed), Value.Type(Table.FirstN(Source, 0)))
in
    Combined

[BA_Pete]

Hi @jomaor1 ,

You can use Text.Split and List.Zip for a very clean process:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQJc1bSUXLUcdIB0YY6RjrGSrE60Uohjn7eQAEXHVcdNyBtomOqY6YUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Serie = _t, Patri = _t]),
    
    addZippedLists = Table.AddColumn(
        Source,
        "ZippedLists",
        each List.Zip(
            {
                Text.Split([Serie], ","),
                Text.Split([Patri], ",")
            }
        )
    ),
    expandZippedLists = Table.ExpandListColumn(addZippedLists, "ZippedLists"),
    extractZippedLists = Table.TransformColumns(expandZippedLists, {"ZippedLists", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    remOthCols = Table.SelectColumns(extractZippedLists,{"Product", "ZippedLists"}),
    splitByDelimiter = Table.SplitColumn(remOthCols, "ZippedLists", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Serie", "Patri"})
in
    splitByDelimiter

Pete

[Akash_Varuna]

Hi , 

Select the Serial column.

Go to the Transform tab and click Split Column > By Delimiter:

Choose your delimiter (comma) and split into Rows.

Repeat the same steps for the Heritage column.
Hope this helps

[jomaor1]

This I already did, but the result for this is the middle table, and I need the 3rd one.

Basically for the first product, I need a row for A->1 B->2 and C->3. And not all the possibilities.

If for example I have 4 products, 10 Serials and 10 Heritages I would only need 40 rows, but your (and mine) method results in 400! (4x10x10)

[Akash_Varuna]

Add a custom column that counts the number of commas in the Seri and Patri columns using the following formulas:

• SeriCount = List.Count(Text.Split([Seri], ","))

• PatriCount = List.Count(Text.Split([Patri], ","))

Expand the Seri and Patri columns using the "Expand" feature, selecting the "Use original column name as prefix" option.

This will create new columns for each value in the Seri and Patri columns.

[jomaor1 · Accepted solution]

Didn't help, but your idea manage to point me in the right direction. Got to work with a duplicate of the same table:

1st Table splits the Serial, 2nd Table splits the Heritage.

Create Index in both of them, and then Merge them together using the Indexes and comum column.

Thanks!