Forum Discussion

jomaor1's avatar
jomaor1
New Member
1 year ago
Solved

Parent Split for two columns

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.

Need Help
  • jomaor1's avatar
    jomaor1
    1 year ago

    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!

6 Replies

  • Akash_Varuna's avatar
    Akash_Varuna
    Super User
    1 year ago

    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's avatar
      jomaor1
      New Member
      1 year ago

      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's avatar
        Akash_Varuna
        Super User
        1 year ago

         

        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.

         

         

  • BA_Pete's avatar
    BA_Pete
    Super User
    1 year ago

    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

  • dufoq3's avatar
    dufoq3
    Community Champion
    1 year ago

    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