Insert variable number of rows based on column value

Question

Hi there!

I am looking for some Power Query hocus pocus, to insert new rows (duplicates) based on a column value.
Next picture descibes the functionality

Based on column [Steps] the current row should be duplicated a number of times, each duplicate reflecting one of the "steps" and its order in the list.
Any hints?

Answer 1

Hi @Arne Bracke

First of all Thanks for providing a clear problem description + expected result. One way:

let  
    // Table for demo:  
    Source = Table.FromRows({  
        {#date(2020,12,11),12345,10,"A / B / C / D"},  
        {#date(2020,12,10),23456,20,"C / D"},  
        {#date(2020,12,12),34567,30,"A / B / C / D"},  
        {#date(2020,12,15),45678,40,"A"}  
    }, type table [Date=date, Order=Int64.Type, Amount=number, Steps=text]),  
    //  
    GroupedRows = Table.Group(Source, {"Date", "Order"},  
        {"DateOrder", each  
            let  
                StepList = Table.AddColumn(_, "Step", each  
                    Text.Split([Steps]," / "), type list  
                ),  
                RemovedSteps = Table.RemoveColumns(StepList,{"Steps"}),  
                ExpandedStep = Table.ExpandListColumn(RemovedSteps, "Step")  
            in  
                Table.AddIndexColumn(ExpandedStep, "StepOrder", 1, 1, Int64.Type),  
            type table  
        }  
    ),  
    Combined = Table.Combine(GroupedRows[DateOrder]),  
    StepTypeText = Table.TransformColumnTypes(Combined,{{"Step", type text}})  
in  
    StepTypeText  

Any question let me know and if this solves your problem please mark this post as answer to help others with a similar scenario - Thanks in advance

Answer 2

Might be more efficient depending on the actual scenario:

let
    // Table for demo:  
    Source = Table.FromRows(
        {  
            {#date(2020,12,11),12345,10,"A / B / C / D"},  
            {#date(2020,12,10),23456,20,"C / D"},  
            {#date(2020,12,12),34567,30,"A / B / C / D"},  
            {#date(2020,12,15),45678,40,"A"}
        },
        type table [Date=date, Order=Int64.Type, Amount=number, Steps=text]
    ),
    StepsTable = Table.AddColumn(Source, "NEW_TABLE", each
        let
            step = Text.Split([Steps], " / "),
            order = List.Numbers(1, List.Count(step))
        in
            Table.FromColumns({step,order}, {"Step", "Step Order"}),
        type table [Step=text, Step Order=Int64.Type]
    ),
    RemovedSteps = Table.RemoveColumns(StepsTable,{"Steps"}),
    ExpandedSteps = Table.ExpandTableColumn(RemovedSteps, "NEW_TABLE", {"Step", "Step Order"})
in
    ExpandedSteps