adding rows based on quanity column

[jag2005]

Hello - I want to be able to in power query to take a list of records and create new copys of each record based on a quantity column.

As a example, I provided a few fields, but I will like for it to copy the entire row as I have 20+ columns.

Current Result
Item Quantity
1234 2
5678 5

Desired Result
Item Quantity New Quantity
1234 2 1
1234 2 1
5678 5 1
5678 5 1
5678 5 1
5678 5 1
5678 5 1

Thanks in advance.

 

[Ron Coderre]

I put your small sample into an Excel Table named Table1
- Add the table to Power Query
then...
These are all of the annotated steps to get your results:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", Int64.Type}, {"Qty", Int64.Type}}),

    // Add a list to each row...List starts at 1 and ends at Qty
    #"Added Custom" = Table.AddColumn(#"Changed Type", "New Qty", each {1..[Qty]}),

    // Expand the list...creating new rows
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "New Qty"),

    // Transform the column..running the Sign option (which returns 1 for each positive value)
    #"Extracted Sign" = Table.TransformColumns(#"Expanded Custom",{{"New Qty", Number.Sign, Int64.Type}})
in
    #"Extracted Sign"

Is that something you can work with?

 

[jag2005]

Hi Ron - this example worked perfectly on my imported source data. Thank you!