Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1082177%22%20slang%3D%22en-US%22%3EHow%20to%20expand%20table%20data%20in%20elaborated%20table%20(as%20shown%20in%20the%20attachment)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1082177%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Every%20one%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHappy%20Chrismas%20and%20New%20Year%20in%20Advance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20to%20expand%20the%20table%20(Table%20A)%20based%20on%20no%20of%20sold%20and%20item.%20It%20should%20be%20shown%20in%20the%20same%20sequence%20in%20one%20column%20(Table%20B)%20itself%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1082177%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1082184%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20expand%20table%20data%20in%20elaborated%20table%20(as%20shown%20in%20the%20attachment)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1082184%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420510%22%20target%3D%22_blank%22%3E%40Sameer_Kuppanath_Sulta%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fde-de%2Fpowerquery-m%2Flist-numbers%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EList.Numbers%3C%2FA%3E%26nbsp%3Bto%20generate%20a%20list%20of%20numbers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1082200%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20expand%20table%20data%20in%20elaborated%20table%20(as%20shown%20in%20the%20attachment)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1082200%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EActually%20I%20want%20to%20list%20out%20unit%20price%20in%20the%20number%20mentioned%20in%20the%20no%20of%20sold.%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20Eg%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGlassi%20Sold%208%20Times.%20So%20i%20want%20to%20list%20out%20glassi%20price%208%20times%20in%20the%20same%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1082206%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20expand%20table%20data%20in%20elaborated%20table%20(as%20shown%20in%20the%20attachment)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1082206%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420510%22%20target%3D%22_blank%22%3E%40Sameer_Kuppanath_Sulta%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20to%20be%20clear%3A%20List.Numbers%20is%20a%20function%20within%20Power%20Query.%3C%2FP%3E%3CP%3ESo%20you%20have%20to%20load%20the%20table%20into%20Power%20Query%20and%20add%20a%20custom%20column%20with%20containing%20this%20function.%3C%2FP%3E%3CP%3ESee%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1082287%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20expand%20table%20data%20in%20elaborated%20table%20(as%20shown%20in%20the%20attachment)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1082287%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethis%20is%20a%20new%20function%20for%20me%2C%20could%20you%20please%20help%20me%20with%20step%20by%20step%3F%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1094499%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20expand%20table%20data%20in%20elaborated%20table%20(as%20shown%20in%20the%20attachment)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1094499%22%20slang%3D%22en-US%22%3E%3CP%3EDoes%20any%20one%20have%20any%20solution%20for%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1094721%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20expand%20table%20data%20in%20elaborated%20table%20(as%20shown%20in%20the%20attachment)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1094721%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420510%22%20target%3D%22_blank%22%3E%40Sameer_Kuppanath_Sulta%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3Bsuggested%20is%20to%20query%20source%20table%20by%20Power%20Query%20and%20made%20small%20transformation%2C%20generated%20script%20is%20looks%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22TableA%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20AddNo%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20Source%2C%0A%20%20%20%20%20%20%20%20%22No%22%2C%0A%20%20%20%20%20%20%20%20each%20List.Numbers(1%2C%5BNoOfTimes%20Sold%5D)%0A%20%20%20%20)%2C%0A%20%20%20%20ExpandNo%20%3D%20Table.ExpandListColumn(%0A%20%20%20%20%20%20%20%20AddNo%2C%20%22No%22%0A%20%20%20%20)%2C%0A%20%20%20%20RemoveUnused%20%3D%20Table.SelectColumns(%0A%20%20%20%20%20%20%20%20ExpandNo%2C%0A%20%20%20%20%20%20%20%20%7B%22Item%22%2C%20%22No%22%2C%20%22Unit%20price%22%7D%0A%20%20%20%20)%0Ain%0A%20%20%20%20RemoveUnused%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20load%20result%20back%20into%20Excel%20sheet.%20It%20is%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

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?

6 Replies
Highlighted
Highlighted

Hi, 

Actually I want to list out unit price in the number mentioned in the no of sold. 

For Eg: 

 

Glassi Sold 8 Times. So i want to list out glassi price 8 times in the same column. 

 

@Detlef Lewin 

Highlighted

@Sameer_Kuppanath_Sulta 

Just to be clear: List.Numbers is a function within Power Query.

So you have to load the table into Power Query and add a custom column with containing this function.

See attached file.

 

 

Highlighted

Hi

 

this is a new function for me, could you please help me with step by step? @Detlef Lewin 

Highlighted

Does any one have any solution for this?

Highlighted

@Sameer_Kuppanath_Sulta 

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.