none
How do I duplicate rows with an index? RRS feed

  • Question

  • I'm merging two sources together and to match granularity, I have to do something like the following:

    turn this:

    into this:

    This is a pretty simple example; I actually have a few more columns, and the index will be appended to text in another column but I think if someone can get me on the right path, I can figure it out. Thanks!

    Thursday, July 24, 2014 4:41 PM

Answers

  • You can say "Add Custom Column" and then enter a column name of "Index" and a formula of "List.Numbers(1, [Quantity])". Then, click the indicator in the header of Index to expand the list values. Finally, remove the Quantity column.

    Here's a complete query that demonstrates this:

    let
        Source = Table.FromRows({{"Apple", 3}, {"Banana", 1}, {"Orange", 2}}, {"Item", "Quantity"}),
        #"Added Custom" = Table.AddColumn(Source, "Index", each List.Numbers(1, [Quantity])),
        #"Expand Index" = Table.ExpandListColumn(#"Added Custom", "Index"),
        #"Removed Columns" = Table.RemoveColumns(#"Expand Index",{"Quantity"})
    in
        #"Removed Columns"

    • Proposed as answer by Curt Hagenlocher Thursday, July 24, 2014 9:43 PM
    • Marked as answer by Strafe Friday, July 25, 2014 5:38 PM
    Thursday, July 24, 2014 9:43 PM

All replies

  • You can say "Add Custom Column" and then enter a column name of "Index" and a formula of "List.Numbers(1, [Quantity])". Then, click the indicator in the header of Index to expand the list values. Finally, remove the Quantity column.

    Here's a complete query that demonstrates this:

    let
        Source = Table.FromRows({{"Apple", 3}, {"Banana", 1}, {"Orange", 2}}, {"Item", "Quantity"}),
        #"Added Custom" = Table.AddColumn(Source, "Index", each List.Numbers(1, [Quantity])),
        #"Expand Index" = Table.ExpandListColumn(#"Added Custom", "Index"),
        #"Removed Columns" = Table.RemoveColumns(#"Expand Index",{"Quantity"})
    in
        #"Removed Columns"

    • Proposed as answer by Curt Hagenlocher Thursday, July 24, 2014 9:43 PM
    • Marked as answer by Strafe Friday, July 25, 2014 5:38 PM
    Thursday, July 24, 2014 9:43 PM
  • I knew it had to be something simple. That worked perfectly, thanks!
    Friday, July 25, 2014 5:38 PM