pivot table lists

[ajm]

hi all, i have a page full of Parent/Class/commodity names. 3 columns where commodity is the basic level of detail. each commodity wraps up to a Class and then each Class belongs to a Parent. there are 121 Classes. In a pivot table, I can add the Class and Commodity fields to the Rows and display the available Commodities grouped by Class.

Class Name	Commodity Name*
Computer Hardware (L2)	Copy Print Fax Scan Equipment (L3)
	Security Computer Hardware Equipment (L3)
	Servers / Storage (L3)
Computer Software (L2)	Custom software (L3)
	IT Software Programs (L3)
	IT Software Programs Electronic Delivery (L3)
	Platform (L3)
	Platform Electronic Delivery (L3)
Construction Materials (L2)	Binders, Stone and Backfill Materials (L3)
	Concrete Materials (L3)
	Construction Materials - Other (L3)
	Contractor Supplied Materials (L3)

No worries....except, I want to generate the Classes as Columns with the available Commodities listed beneath.

Computer Hardware (L2)	Computer Software (L2)	Construction Materials (L2)
Copy Print Fax Scan Equipment (L3)	Custom software (L3)	Binders, Stone and Backfill Materials (L3)
Security Computer Hardware Equipment (L3)	IT Software Programs (L3)	Concrete Materials (L3)
Servers / Storage (L3)	IT Software Programs Electronic Delivery (L3)	Construction Materials - Other (L3)
	Platform (L3)	Contractor Supplied Materials (L3)
	Platform Electronic Delivery (L3)	Fencing and Gates (L3)

Any help would be appreciated.

I am using Microsoft® Excel® for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20348) 64-bit

 

[alansidman]

Pivot your data with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Class Name"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Class Name"}, {{"Data", each _, type table [Class Name=text, #"Commodity Name*"=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Commodity Name*", "Index"}, {"Custom.Commodity Name*", "Custom.Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[#"Class Name"]), "Class Name", "Custom.Commodity Name*"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom.Index"})
in
    #"Removed Columns1"

Book5
	A	B	C	D	E	F
1	Class Name	Commodity Name*		Computer Hardware (L2)	Computer Software (L2)	Construction Materials (L2)
2	Computer Hardware (L2)	Copy Print Fax Scan Equipment (L3)		Copy Print Fax Scan Equipment (L3)	Custom software (L3)	Binders, Stone and Backfill Materials (L3)
3		Security Computer Hardware Equipment (L3)		Security Computer Hardware Equipment (L3)	IT Software Programs (L3)	Concrete Materials (L3)
4		Servers / Storage (L3)		Servers / Storage (L3)	IT Software Programs Electronic Delivery (L3)	Construction Materials - Other (L3)
5	Computer Software (L2)	Custom software (L3)			Platform (L3)	Contractor Supplied Materials (L3)
6		IT Software Programs (L3)			Platform Electronic Delivery (L3)
7		IT Software Programs Electronic Delivery (L3)
8		Platform (L3)
9		Platform Electronic Delivery (L3)
10	Construction Materials (L2)	Binders, Stone and Backfill Materials (L3)
11		Concrete Materials (L3)
12		Construction Materials - Other (L3)
13		Contractor Supplied Materials (L3)
Sheet1

 

[ajm]

thanks Alan. perfect.