Forum Discussion

Sameer_Kuppanath_Sultan's avatar
Sameer_Kuppanath_Sultan
Brass Contributor
Dec 28, 2019

How to expand table data in elaborated table (as shown in the attachment)

Hi Every one   Happy Chrismas and New Year in Advance   How to expand the table (Table A) based on no of sold and item. It should be shown in the same sequence in one column (Table B) itself?
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 07, 2020

    Sameer_Kuppanath_Sultan 

    What Detlef_Lewin suggested is to query source table by Power Query and made small transformation, generated script is looks like

    let
    Source = Excel.CurrentWorkbook(){[Name="TableA"]}[Content],
    AddNo = Table.AddColumn(
        Source,
        "No",
        each List.Numbers(1,[NoOfTimes Sold])
    ),
    ExpandNo = Table.ExpandListColumn(
        AddNo, "No"
    ),
    RemoveUnused = Table.SelectColumns(
        ExpandNo,
        {"Item", "No", "Unit price"}
    )
in
    RemoveUnused

    and load result back into Excel sheet. It is in attached file.

Resources