none
PowerQuery- generate rows RRS feed

  • Question

  • Hello,

    I would like to have my powerquery to create new rows starting from a record. Essentially, duplicating rows based on a value within a field.

    Is that possible? any ideas or workarounds?

    Example:

    Input:

    Region Sales

    A          3

    Output:

    Region Sales

    A          3

    A          3

    A          3

    Thanks

    Luca

    Tuesday, October 3, 2017 6:53 PM

Answers

  • Alternatively you can add a temporary column with a list of all numbers 1-[Sales], expand the column and remove it again.

    let
        Source = Input,
        #"Added Custom" = Table.AddColumn(Source, "Custom", each {1..[Sales]}),
        #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Custom"})
    in
        #"Removed Columns"

    • Marked as answer by Jukas77 Tuesday, October 3, 2017 7:50 PM
    Tuesday, October 3, 2017 7:28 PM

All replies

  • Hey,

    This is the function that you're looking for.

    You'd create a custom column with a formula like: List.Repeat({_}, [SalesField]) 

    Tuesday, October 3, 2017 7:22 PM
  • Alternatively you can add a temporary column with a list of all numbers 1-[Sales], expand the column and remove it again.

    let
        Source = Input,
        #"Added Custom" = Table.AddColumn(Source, "Custom", each {1..[Sales]}),
        #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Custom"})
    in
        #"Removed Columns"

    • Marked as answer by Jukas77 Tuesday, October 3, 2017 7:50 PM
    Tuesday, October 3, 2017 7:28 PM