Advanced Transpose help

[cornwithbutter]

Dear Excel and data experts,

I would like to seek your help to create a formula/macro.
I currently have a product spreadsheet in a table format which I need to convert into a list for uploading onto the sales systems. The problem is I am not sure where/how to start off and was wondering if there is a term for such transpose method whereby both row/column data fields are transposed. Also for Row 4 and Column G are related so that I can combine to return the full product code. Currently I converting it with concatenate formula but this involves manually work. The catalogue contains almost 2000 price entries.

It would be grateful if you could point me in the right direction or similar cases I could reference from. Thank you very much!

 

[Kerryx]

With Power Query and amending source data ( split it into 2 tables) a small tweak, think this works

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1						Column1	Column2	Column3	Column4	Column5	Column6		ORG	DES	Area	Org Code	Ext	Query1.Column2	Query1.Column3	Query1.Column4	ProductCode	Value
2						P CODE		L-XXXX	Z-xxxx	P-xxxxx	B-xxxxx		Australia	Korea	Oceania	AU	81	38	x3	c2	BFxxxxx	1500
3						Prod Info 1		38	38	38	38		Australia	Korea	Oceania	AU	81	38	x3	c2	BFxxxxx	1000
4						Prod Info 2		x2	x2	x3	x3		Australia	Korea	Oceania	AU	81	38	x3	c2	BSxxxxx	1000
5						Prod Info 3		c1	c1	c2	c2		USA	Korea	US	US	61	38	x3	c2	BSxxxxx	500
6	ORG	DES	Area	Org Code	Ext	SeasNme	SEASON	L-XXXX	Z-xxxx	P-xxxxx	B-xxxxx		Australia	Korea	Oceania	AU	81	38	x3	c2	BWxxxxx	2000
7	Japan	Korea	Asia	JP	81	Winter	W	200		700	1500		Japan	Korea	Asia	JP	81	38	x3	c2	BWxxxxx	1500
8	Australia	Korea	Oceania	AU	81	Fall	F		250	450	1000		Australia	Korea	Oceania	AU	81	38	x2	c1	LFXXXX	450
9	USA	Korea	US	US	61	Summer	S	150	160		500		USA	Korea	US	US	61	38	x2	c1	LSXXXX	150
10	Australia	Korea	Oceania	AU	81	Winter	W	650		800	2000		Australia	Korea	Oceania	AU	81	38	x2	c1	LSXXXX	280
11	Australia	Korea	Oceania	AU	81	Fall	F	450	350		1500		Japan	Korea	Asia	JP	81	38	x2	c1	LWXXXX	200
12	Australia	Korea	Oceania	AU	81	Summer	S	280	200	600	1000		Australia	Korea	Oceania	AU	81	38	x2	c1	LWXXXX	650
13													Australia	Korea	Oceania	AU	81	38	x3	c2	PFxxxxx	450
14													Australia	Korea	Oceania	AU	81	38	x3	c2	PSxxxxx	600
15													Japan	Korea	Asia	JP	81	38	x3	c2	PWxxxxx	700
16													Australia	Korea	Oceania	AU	81	38	x3	c2	PWxxxxx	800
17													Australia	Korea	Oceania	AU	81	38	x2	c1	ZFxxxx	250
18													Australia	Korea	Oceania	AU	81	38	x2	c1	ZFxxxx	350
19													Australia	Korea	Oceania	AU	81	38	x2	c1	ZSxxxx	200
20													USA	Korea	US	US	61	38	x2	c1	ZSxxxx	160
Sheet1

 

[Kerryx]

Table 4 which is Product info is imported to PQ and transposed to allow mapping to table 5

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column2"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns")
in
    #"Transposed Table"

Table 5 which has prices etc is brought in , here are applied steps

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ORG", type text}, {"DES", type text}, {"Area", type text}, {"Org Code", type text}, {"Ext", Int64.Type}, {"SeasNme", type text}, {"SEASON", type text}, {"L-XXXX", Int64.Type}, {"Z-xxxx", Int64.Type}, {"P-xxxxx", Int64.Type}, {"B-xxxxx", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ORG", "DES", "Area", "Org Code", "Ext", "SeasNme", "SEASON"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "ProductCode", each Text.BeforeDelimiter([Attribute],"-") &[SEASON]&Text.AfterDelimiter([Attribute],"-")),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"ORG", "DES", "Area", "Org Code", "Ext", "SeasNme", "SEASON", "Attribute", "ProductCode", "Value"}),
    #"Merged Queries" = Table.NestedJoin(#"Reordered Columns", {"Attribute"}, #"Table 4", {"Column1"}, "Query1", JoinKind.LeftOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Column1", "Column2", "Column3", "Column4"}, {"Query1.Column1", "Query1.Column2", "Query1.Column3", "Query1.Column4"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Query1",{"Query1.Column1"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"ORG", "DES", "Area", "Org Code", "Ext", "Query1.Column2", "Query1.Column3", "Query1.Column4", "SeasNme", "SEASON", "Attribute", "ProductCode", "Value"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns1",{"SeasNme", "SEASON", "Attribute"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"ProductCode", Order.Ascending}})
in
    #"Sorted Rows"

 

[cornwithbutter]

Thank you so much I followed the steps and managed to generate the list as intended! You are a life saver

 

[cornwithbutter]

Table 4 which is Product info is imported to PQ and transposed to allow mapping to table 5

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column2"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns")
in
    #"Transposed Table"

View attachment 93637

Table 5 which has prices etc is brought in , here are applied steps

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ORG", type text}, {"DES", type text}, {"Area", type text}, {"Org Code", type text}, {"Ext", Int64.Type}, {"SeasNme", type text}, {"SEASON", type text}, {"L-XXXX", Int64.Type}, {"Z-xxxx", Int64.Type}, {"P-xxxxx", Int64.Type}, {"B-xxxxx", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ORG", "DES", "Area", "Org Code", "Ext", "SeasNme", "SEASON"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "ProductCode", each Text.BeforeDelimiter([Attribute],"-") &[SEASON]&Text.AfterDelimiter([Attribute],"-")),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"ORG", "DES", "Area", "Org Code", "Ext", "SeasNme", "SEASON", "Attribute", "ProductCode", "Value"}),
    #"Merged Queries" = Table.NestedJoin(#"Reordered Columns", {"Attribute"}, #"Table 4", {"Column1"}, "Query1", JoinKind.LeftOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Column1", "Column2", "Column3", "Column4"}, {"Query1.Column1", "Query1.Column2", "Query1.Column3", "Query1.Column4"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Query1",{"Query1.Column1"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"ORG", "DES", "Area", "Org Code", "Ext", "Query1.Column2", "Query1.Column3", "Query1.Column4", "SeasNme", "SEASON", "Attribute", "ProductCode", "Value"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns1",{"SeasNme", "SEASON", "Attribute"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"ProductCode", Order.Ascending}})
in
    #"Sorted Rows"

View attachment 93638

Thank you so much I followed the steps and managed to generate the list as intended! You are a life saver

 

[Kerryx]

Glad to help