Insert variable number of rows based on column value

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

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

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

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
Text.Split([Steps]," / "), type list
),
RemovedSteps = Table.RemoveColumns(StepList,{"Steps"}),
ExpandedStep = Table.ExpandListColumn(RemovedSteps, "Step")
in
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. 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]
),