Insert variable number of rows based on column value

Arne Bracke 26 Reputation points
2020-12-11T12:01:04.333+00:00

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

47349-image.png

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?

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
36,596 questions
0 comments No comments
{count} votes

Accepted answer
  1. Lz._ 8,991 Reputation points
    2020-12-11T13:00:13.277+00:00

    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


1 additional answer

Sort by: Most helpful
  1. Lz._ 8,991 Reputation points
    2023-05-13T09:43:57.4633333+00:00

    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
    
    0 comments No comments