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