Transpose Large Data Help

[devint]

Morning,

Hoping someone might have knowledge that may help me. I have to run a data pull every month. It comes in a certain format, and I need to transpose it into another format. I understand the transpose part, but only for a single set of data. My data is about 6000 rows long.

This is the format the data comes in:

G80012
ASHGREY	S​	M​	L​	XL​	2XL​	3XL​	4XL​	5XL​
RegularPrice	$2.24​	$2.24​	$2.24​	$2.24​	$4.43​	$6.03​	$5.98​	$5.99​
SpecialPrice	$1.92​	$1.92​	$1.92​	$1.92​
Total-Inventory	27322​	4868​	16127​	2190​	78​	0​	10​	11​
AZALEA	S​	M​	L​	XL​	2XL​	3XL​	4XL​	5XL​
RegularPrice	$2.32​	$2.32​	$2.32​	$2.32​	$4.91​	$6.34​	$6.28​	$6.28​
SpecialPrice	$2.03​	$2.03​	$2.03​	$2.03​
Total-Inventory	1646​	2000​	1355​	436​	427​	70​	0​	0​
BLACK	S​	M​	L​	XL​	2XL​	3XL​	4XL​	5XL​
RegularPrice	$2.32​	$2.32​	$2.32​	$2.32​	$4.91​	$6.34​	$6.28​	$6.28​
SpecialPrice	$2.03​	$2.03​	$2.03​	$2.03​
Total-Inventory	35935​	56924​	67178​	28203​	10723​	5075​	144​	1006​

My ideal end result would look like this:

Style​	Color​	Size​	RegularPrice​	SpecialPrice​	Total-Inventory​
G80012​	ASHGREY​	S​	$2.24​	$1.92​	27322​
G80012​	ASHGREY​	M​	$2.24​	$1.92​	4868​
G80012​	ASHGREY​	L​	$2.24​	$1.92​	16127​
G80012​	ASHGREY​	XL​	$2.24​	$1.92​	2190​
G80012​	ASHGREY​	2XL​	$4.43​		78​
G80012​	ASHGREY​	3XL​	$6.03​		0​
G80012​	ASHGREY​	4XL​	$5.98​		10​
G80012​	ASHGREY​	5XL​	$5.99​		11​
G80012​	AZALEA​	S​	$2.32​	$2.03​	1646​
G80012​	AZALEA​	M​	$2.32​	$2.03​	2000​
G80012​	AZALEA​	L​	$2.32​	$2.03​	1355​
G80012​	AZALEA​	XL​	$2.32​	$2.03​	436​
G80012​	AZALEA​	2XL​	$4.91​		427​
G80012​	AZALEA​	3XL​	$6.34​		70​
G80012​	AZALEA​	4XL​	$6.28​		0​
G80012​	AZALEA​	5XL​	$6.28​		0​
G80012​	BLACK​	S​	$2.32​	$2.03​	35935​
G80012​	BLACK​	M​	$2.32​	$2.03​	56924​
G80012​	BLACK​	L​	$2.32​	$2.03​	67178​
G80012​	BLACK​	XL​	$2.32​	$2.03​	28203​
G80012​	BLACK​	2XL​	$4.91​		10723​
G80012​	BLACK​	3XL​	$6.34​		5075​
G80012​	BLACK​	4XL​	$6.28​		144​
G80012​	BLACK​	5XL​	$6.28​		1006​

I can do a transpose and manually copy/paste each 'color', but with 150 colors this would take me a long time. Was hoping there was a formula or macro that can achieve this with a few strokes.

This is beyond my knowledge of excel, so hoping I can find help here.

Thanks,

 

[devint]

You are amazing man. Appreciate all your help, this saved me hours of work a month.

 

[Peter_SSs]

You're welcome. Thanks for your kind words.

 

[alansidman]

Sorry to be late to the party, but here is a Power Query Solution.

Book3
	A	B	C	D	E	F
1	Color	Style	Size	RegularPrice	SpecialPrice	Total-Inventory
2	ASHGREY	G801	S	2.24	1.92	27322
3	ASHGREY	G802	M	2.24	1.92	4868
4	ASHGREY	G803	L	2.24	1.92	16127
5	ASHGREY	G804	XL	2.24	1.92	2190
6	ASHGREY	G805	2XL	4.43		78
7	ASHGREY	G806	3XL	6.03		0
8	ASHGREY	G807	4XL	5.98		10
9	ASHGREY	G808	5XL	5.99		11
10	AZALEA	G801	S	2.32	2.03	1646
11	AZALEA	G802	M	2.32	2.03	2000
12	AZALEA	G803	L	2.32	2.03	1355
13	AZALEA	G804	XL	2.32	2.03	436
14	AZALEA	G805	2XL	4.91		427
15	AZALEA	G806	3XL	6.34		70
16	AZALEA	G807	4XL	6.28		0
17	AZALEA	G808	5XL	6.28		0
18	BLACK	G801	S	2.32	2.03	35935
19	BLACK	G802	M	2.32	2.03	56924
20	BLACK	G803	L	2.32	2.03	67178
21	BLACK	G804	XL	2.32	2.03	28203
22	BLACK	G805	2XL	4.91		10723
23	BLACK	G806	3XL	6.34		5075
24	BLACK	G807	4XL	6.28		144
25	BLACK	G808	5XL	6.28		1006
26	CAROLINABLUE	G801	S	2.32	2.03	27833
27	CAROLINABLUE	G802	M	2.32	2.03	5947
28	CAROLINABLUE	G803	L	2.32	2.03	3580
29	CAROLINABLUE	G804	XL	2.32	2.03	4363
30	CAROLINABLUE	G805	2XL	4.91		4179
31	CAROLINABLUE	G806	3XL	6.34		556
32	CAROLINABLUE	G807	4XL	6.28		0
33	CAROLINABLUE	G808	5XL	6.28		0
34	DAISY	G801	S	2.32		27886
35	DAISY	G802	M	2.32		10266
36	DAISY	G803	L	2.32		4470
37	DAISY	G804	XL	2.32		9997
38	DAISY	G805	2XL	4.91		1126
39	DAISY	G806	3XL	6.34		2584
40	DAISY	G807	4XL	6.28		9
41	DAISY	G808	5XL	6.28		0
42	DARKCHOCOLATE	G801	S	2.56	2.03	0
43	DARKCHOCOLATE	G802	M	2.56	0.75	0
44	DARKCHOCOLATE	G803	L	2.56	2.03	51
45	DARKCHOCOLATE	G804	XL	2.56	2.03	0
46	DARKCHOCOLATE	G805	2XL	4.46		0
47	DARKCHOCOLATE	G806	3XL	5.99		0
48	DARKCHOCOLATE	G807	4XL	5.99		0
49	DARKCHOCOLATE	G808	5XL	5.99		0
50	DARKHEATHER	G801	S	2.32	2.03	22425
51	DARKHEATHER	G802	M	2.32	2.03	9243
52	DARKHEATHER	G803	L	2.32	2.03	13362
53	DARKHEATHER	G804	XL	2.32	2.03	3286
54	DARKHEATHER	G805	2XL	4.91		2633
55	DARKHEATHER	G806	3XL	6.34		0
56	DARKHEATHER	G807	4XL	6.28		0
57	DARKHEATHER	G808	5XL	6.28		31
58	ELECTRICGREEN	G801	S	2.32	2.03	20806
59	ELECTRICGREEN	G802	M	2.32	2.03	21523
60	ELECTRICGREEN	G803	L	2.32	2.03	19842
61	ELECTRICGREEN	G804	XL	2.32	2.03	12098
62	ELECTRICGREEN	G805	2XL	4.91		3409
63	ELECTRICGREEN	G806	3XL	6.34		796
64	ELECTRICGREEN	G807	4XL	6.28		313
65	ELECTRICGREEN	G808	5XL	6.28		0
Table1 (2)

Mcode:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"G800", type text}, {"G801", type any}, {"G802", type any}, {"G803", type any}, {"G804", type any}, {"G805", type any}, {"G806", type any}, {"G807", type any}, {"G808", type any}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if[G801]= "S" then[G800] else null),
    #"Demoted Headers" = Table.DemoteHeaders(#"Added Custom"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Merged Columns" = Table.CombineColumns(#"Promoted Headers",{"G800", "ASHGREY"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Demoted Headers1" = Table.DemoteHeaders(#"Merged Columns"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Demoted Headers1",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type text}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type text}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type text}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type text}, {"Column18", type any}, {"Column19", type any}, {"Column20", type text}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type text}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type text}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}}),
    #"Transposed Table1" = Table.Transpose(#"Changed Type2"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Filled Down" = Table.FillDown(#"Promoted Headers1",{"Custom:ASHGREY"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down",null,"ASHGREY",Replacer.ReplaceValue,{"Custom:ASHGREY"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Replaced Value",{"Custom:ASHGREY", "Merged", "G801:S", "G802:M", "G803:L", "G804:XL", "G805:2XL", "G806:3XL", "G807:4XL", "G808:5XL"}),
    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([#"G801:S"] <> "S")),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Custom:ASHGREY", "Merged"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Merged"}, {{"Data", each _, type table [#"Custom:ASHGREY"=text, Merged=text, Attribute=text, Value=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index", 1, 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Custom:ASHGREY", "Attribute", "Value", "Index"}, {"Custom:ASHGREY", "Attribute", "Value", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom:ASHGREY", "Color"}}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Renamed Columns", "Merged", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Merged.1", "Merged.2"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Character Transition",{"Merged.2"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1", "Merged.1", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Merged.1.1", "Merged.1.2"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Split Column by Delimiter",{"Merged.1.2"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[Merged.1.1]), "Merged.1.1", "Value"),
    #"Removed Columns3" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns3", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Attribute.2", "Size"}, {"Attribute.1", "Style"}})
in
    #"Renamed Columns1"