Create table from list of items

M

mike.magill

New member
Joined
Jan 24, 2017
Messages
6
Reaction score
0
Points
0
Excel Version(s)
2016
I have a table with a list of items and an integer against each item. I want to create a separate table with multiple rows for each item according to the integer and each row numbered (i.e)

Initial table

item A, 3
item B, 4

New table

item A, 1
item A, 2
item A, 3
item B, 1
item B, 2
item B, 3
item B, 4

Can anyone help? I know I should you List.Generate but can't work out the syntax
 
Here is the result using PQ

vAB
1Column1Custom
2item A1
3item A2
4item A3
5item B1
6item B2
7item B3
8item B4

Mcode

//Table7 change to your table name


Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each {Number.From(1)..([Column2])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Column2"})
in
    #"Removed Columns"
 
Perfect! And so much simpler than I had imagined. Thanks.
 
You must log in or register to reply here.
Back
Top