Results 1 to 3 of 3

Thread: Create table from list of items

  1. #1
    Seeker mike.magill's Avatar
    Join Date
    Jan 2017
    Posts
    5
    Articles
    0
    Excel Version
    2016

    Create table from list of items



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    155
    Articles
    0
    Excel Version
    2019
    Here is the result using PQ

    v A B
    1 Column1 Custom
    2 item A 1
    3 item A 2
    4 item A 3
    5 item B 1
    6 item B 2
    7 item B 3
    8 item B 4

    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"

  3. #3
    Seeker mike.magill's Avatar
    Join Date
    Jan 2017
    Posts
    5
    Articles
    0
    Excel Version
    2016
    Perfect! And so much simpler than I had imagined. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •