Excel formatting problem pivot/unpivot

[Anunay]

This is the source format i have which i have tried picoting and unpivoting and even transposing for some reason to make it work in the format i have attached below i have even reached a point where my excel looks almost like the output but then the batch numbers are missing.

Order Problem Source.xlsx
	A	B	C	D	E	F
1				94417	94418	94419
2	Item Type	Item No	Item Description	Completed	Completed	WIP
3	Ingredients	F2200000000TK	Batter	1.000	2.000	3.000
4	Ingredients	F220000000000	Honey	2.000	3.000	4.000
5	Ingredients	F22000000FXTK	ChocoChip	3.000	4.000	5.000
Sheet1

This is output i want

Order Problem Source.xlsx
	A	B	C	D	E	F
1	Order Batch ID	Item Type	Item No	Item Description	Qty	Status
2	Batch No	Item Type	Item No	Item Description	Qty	Status
3	94417	Ingredients	F2200000000TK	Batter	1	Completed
4	94417	Ingredients	F2200000000TK	Batter	2	Completed
5	94417	Ingredients	F2200000000TK	Batter	3	WIP
6	94418	Ingredients	F220000000000	Honey	1	Completed
7	94418	Ingredients	F220000000000	Honey	2	Completed
8	94418	Ingredients	F220000000000	Honey	3	WIP
9	94419	Ingredients	F22000000FXTK	ChocoChip	3	Completed
10	94419	Ingredients	F22000000FXTK	ChocoChip	4	Completed
11	94419	Ingredients	F22000000FXTK	ChocoChip	5	WIP
Sheet2

This is my solution which is not complete as the order batch id column went missing after the unpivoting steps.

Book3
	A	B	C	D	E
1	Item Type	Item No	Item Description	Attribute	Value
2	Ingredients	F2200000000TK	Batter	Completed	1
3	Ingredients	F2200000000TK	Batter	Complete	2
4	Ingredients	F2200000000TK	Batter	WIP	3
5	Ingredients	F220000000000	Honey	Completed	2
6	Ingredients	F220000000000	Honey	Complete	3
7	Ingredients	F220000000000	Honey	WIP	4
8	Ingredients	F22000000FXTK	Chocochip	Completed	3
9	Ingredients	F22000000FXTK	Chocochip	Complete	4
10	Ingredients	F22000000FXTK	Chocochip	WIP	5
Table1

 

[Anunay]

Solution using VBA or Power Query are both welcome

 

[Alex Blakenburg]

Here is a power query version.
You do need to convert the data to a table first and in that process unclick the Table has headers. You can then unclick the Table Design > Header Row, to hide the Column1, Column2 etc row it creates.
If you call your table "tblData", then you can use the query as is, otherwise just change the source to your table name.

PS: This technique is used by Leila Gharan ref:

Wyn Hopkins uses a slightly different technique using a Range Name instead of a table name

let
    Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Source,{"Column1", "Column2", "Column3"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Transposed Table" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Item Type|Item No|Item Description", "||"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"||", "Order Batch ID"}, {"Item Type|Item No|Item Description", "Status"}, {"Attribute.1", "Item Type"}, {"Attribute.2", "Item No"}, {"Attribute.3", "Item Description"}, {"Value", "Qty"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Order Batch ID", "Item Type", "Item No", "Item Description", "Qty", "Status"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Order Batch ID", Int64.Type}, {"Item Type", type text}, {"Item No", type text}, {"Item Description", type text}, {"Qty", Int64.Type}, {"Status", type text}})
in
    #"Changed Type"

 

[Anunay]

When you enter power query you automatically convert the selected data into a table, I couldn't cogently understand your solution tbh.

 

[Anunay]

Here is a power query version.
You do need to convert the data to a table first and in that process unclick the Table has headers. You can then unclick the Table Design > Header Row, to hide the Column1, Column2 etc row it creates.
If you call your table "tblData", then you can use the query as is, otherwise just change the source to your table name.

PS: This technique is used by Leila Gharan ref:

Wyn Hopkins uses a slightly different technique using a Range Name instead of a table name

let
    Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Source,{"Column1", "Column2", "Column3"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Transposed Table" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Item Type|Item No|Item Description", "||"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"||", "Order Batch ID"}, {"Item Type|Item No|Item Description", "Status"}, {"Attribute.1", "Item Type"}, {"Attribute.2", "Item No"}, {"Attribute.3", "Item Description"}, {"Value", "Qty"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Order Batch ID", "Item Type", "Item No", "Item Description", "Qty", "Status"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Order Batch ID", Int64.Type}, {"Item Type", type text}, {"Item No", type text}, {"Item Description", type text}, {"Qty", Int64.Type}, {"Status", type text}})
in
    #"Changed Type"

here's my personal mail anunaysharma1@gmail.com send me a mail and maybe if you got time we'll connect on meet and you could show me how to do this, because i'm going berserk not being able to solve this.

 

[Anunay]

Ignore all replies this was mad genius. I did it. It worked!!!!!

 

[Alex Blakenburg]

Glad you liked it and can see the possibilities.