Arrange (Transpose) Single Row to Multiple Row Based on Criteria

[ESCAAGROVET]

Hi Friends,
I have Product Data in Single Row in following order (data in tabular form at end of sheet)

Above data is required to be sorted in new sheet as following

1. Row number 2,3,4, are for refrence only. Not Required In actual output
2. If Varient 1 or varient 2 Data is blank it needs to be skipped

Your help will be of immense help.
Thanks to all of you in advance for giving precious time of yours.

#

Unique ID

Variant ID

Item Name

Vendor

Category

Packing

Packing Unit

Weight (gm)

Barcode

Image

HSN

Tax Rate

Varient ID 1

Packing

Packing Unit 1

Weight 1

Varient ID 2

Packing

Packing Unit 2

Weight 2

1

10000

10000

ITEM A

VEND 1

CATG 1

Packing

10Kg

10000

10000

https://cdn.shopify.com/s/files/1/0270/5499/7569/products/ARAROTLOOSE.jpg?v=1607366547

1101

00

10000 B

Packing

5Kg

5000

10000 C

Packing

50Kg

50000

2

10001

10001

ITEM B

VEND 2

CATG 2

Packing

100Kg

100000

10001

https://cdn.shopify.com/s/files/1/0270/5499/7569/products/ARAROTLOOSE.jpg?v=1607366547

1102

00

10001 B

Packing

15Kg

15000

3

10002

10002

ITEM C

VEND 3

CATG 3

Packing

1000Kg

1000000

10002

https://cdn.shopify.com/s/files/1/0270/5499/7569/products/ARAROTLOOSE.jpg?v=1607366547

1103

00

4

10003

10003

ITEM D

VEND 4

CATG 4

Packing

10001Kg

1000100

10003

https://cdn.shopify.com/s/files/1/0270/5499/7569/products/ARAROTLOOSE.jpg?v=1607366548

1104

00

 

[alansidman]

With the exception of Sold by Weight, I believe the following Mcode accomplishes what you are looking for. This is a Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type any}, {"Column10", type any}, {"Column11", type text}, {"Column12", type any}, {"Column13", type any}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type any}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type any}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"#", Int64.Type}, {"Unique ID", Int64.Type}, {"Variant ID", Int64.Type}, {"Item Name", type text}, {"Vendor", type text}, {"Category", type text}, {"Packing", type text}, {"Packing Unit", type text}, {"Weight (gm)", Int64.Type}, {"Barcode", Int64.Type}, {"Image", type text}, {"HSN", Int64.Type}, {"Tax Rate", Int64.Type}, {"Varient ID 1", type text}, {"Packing_1", type text}, {"Packing Unit 1", type text}, {"Weight 1", Int64.Type}, {"Varient ID 2", type text}, {"Packing_2", type text}, {"Packing Unit 2", type text}, {"Weight 2", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Unique ID", "Variant ID", "Item Name", "Category"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each Text.StartsWith([Attribute], "Packing Unit"))
in
    #"Filtered Rows"

Need to only change the Field Names for the last two columns.

Book1
	A	B	C	D	E	F
1	Unique ID	Variant ID	Item Name	Category	Attribute	Value
2	10000	10000	ITEM A	CATG 1	Packing Unit	10Kg
3	10000	10000	ITEM A	CATG 1	Packing Unit 1	5Kg
4	10000	10000	ITEM A	CATG 1	Packing Unit 2	50Kg
5	10001	10001	ITEM B	CATG 2	Packing Unit	100Kg
6	10001	10001	ITEM B	CATG 2	Packing Unit 1	15Kg
7	10002	10002	ITEM C	CATG 3	Packing Unit	1000Kg
8	10003	10003	ITEM D	CATG 4	Packing Unit	10001Kg
Table1

 

[ESCAAGROVET]

With the exception of Sold by Weight, I believe the following Mcode accomplishes what you are looking for. This is a Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type any}, {"Column10", type any}, {"Column11", type text}, {"Column12", type any}, {"Column13", type any}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type any}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type any}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"#", Int64.Type}, {"Unique ID", Int64.Type}, {"Variant ID", Int64.Type}, {"Item Name", type text}, {"Vendor", type text}, {"Category", type text}, {"Packing", type text}, {"Packing Unit", type text}, {"Weight (gm)", Int64.Type}, {"Barcode", Int64.Type}, {"Image", type text}, {"HSN", Int64.Type}, {"Tax Rate", Int64.Type}, {"Varient ID 1", type text}, {"Packing_1", type text}, {"Packing Unit 1", type text}, {"Weight 1", Int64.Type}, {"Varient ID 2", type text}, {"Packing_2", type text}, {"Packing Unit 2", type text}, {"Weight 2", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Unique ID", "Variant ID", "Item Name", "Category"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each Text.StartsWith([Attribute], "Packing Unit"))
in
    #"Filtered Rows"

Need to only change the Field Names for the last two columns.

Book1
	A	B	C	D	E	F
1	Unique ID	Variant ID	Item Name	Category	Attribute	Value
2	10000	10000	ITEM A	CATG 1	Packing Unit	10Kg
3	10000	10000	ITEM A	CATG 1	Packing Unit 1	5Kg
4	10000	10000	ITEM A	CATG 1	Packing Unit 2	50Kg
5	10001	10001	ITEM B	CATG 2	Packing Unit	100Kg
6	10001	10001	ITEM B	CATG 2	Packing Unit 1	15Kg
7	10002	10002	ITEM C	CATG 3	Packing Unit	1000Kg
8	10003	10003	ITEM D	CATG 4	Packing Unit	10001Kg
Table1

Thanks for your time,
Item Name, Category & Packing should not repeat in output. Kindly help when possible.