Insert variable number of rows based on column value

Arne Bracke 26 Reputation points

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?

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
25,351 questions
No comments
{count} votes

Accepted answer
  1. Lz._ 8,806 Reputation points

    Hi @Arne Bracke

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

        // 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"},  
        }, type table [Date=date, Order=Int64.Type, Amount=number, Steps=text]),  
        GroupedRows = Table.Group(Source, {"Date", "Order"},  
            {"DateOrder", each  
                    StepList = Table.AddColumn(_, "Step", each  
                        Text.Split([Steps]," / "), type list  
                    RemovedSteps = Table.RemoveColumns(StepList,{"Steps"}),  
                    ExpandedStep = Table.ExpandListColumn(RemovedSteps, "Step")  
                    Table.AddIndexColumn(ExpandedStep, "StepOrder", 1, 1, Int64.Type),  
                type table  
        Combined = Table.Combine(GroupedRows[DateOrder]),  
        StepTypeText = Table.TransformColumnTypes(Combined,{{"Step", type text}})  

    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

0 additional answers

Sort by: Most helpful