Depending on the data and requirements we can not Unpivot the items. As per my knowledge I have created separate table for each department and then combined all in the end.
However, I could not populate the rejection column, instead the quantity is available in the row.
Main Data format as per requirement
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Top Rows" = Table.Skip(Source,2),
#"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows",{{"Column1", "Order#"}, {"Column2", "IP#"}, {"Column3", "Line#"}, {"Column4", "Style"}, {"Column5", "PO#"}, {"Column6", "Color"}, {"Column7", "Order Qty"}, {"Column8", "Price"}})
in
#"Renamed Columns"
Cutting Batch 1
let
Source = #"Main Data",
#"Removed Columns" = Table.RemoveColumns(Source,{"Stitching", "Stitching12", "Stitching13", "Stitching14", "Stitching15", "Stitching16", "Finishing", "Finishing17", "Finishing18", "Finishing19", "Rejection", "Cutting10", "Cutting11"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Cutting] <> null)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Cutting", "Date"}, {"Cutting9", "Qty"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Department", each "Cutting")
in
#"Added Custom"
Cutting Batch 2
let
Source = #"Main Data",
#"Removed Columns" = Table.RemoveColumns(Source,{"Cutting", "Cutting9", "Stitching", "Stitching12", "Stitching13", "Stitching14", "Stitching15", "Stitching16", "Finishing", "Finishing17", "Finishing18", "Finishing19", "Rejection"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Cutting10] <> null)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Cutting10", "Date"}, {"Cutting11", "Qty"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Department", each "Cutting")
in
#"Added Custom"
Stitching Batch 1
let
Source = #"Main Data",
#"Removed Columns" = Table.RemoveColumns(Source,{"Cutting", "Cutting9", "Cutting10", "Cutting11", "Finishing", "Finishing17", "Finishing18", "Finishing19", "Rejection", "Stitching13", "Stitching14", "Stitching15", "Stitching16"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Stitching] <> null)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Stitching", "Date"}, {"Stitching12", "Qty"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Department", each "Stitching")
in
#"Added Custom"
Stitching Batch 2
let
Source = #"Main Data",
#"Removed Columns" = Table.RemoveColumns(Source,{"Cutting", "Cutting9", "Cutting10", "Cutting11", "Stitching", "Stitching12", "Stitching15", "Stitching16", "Finishing", "Finishing17", "Finishing18", "Finishing19", "Rejection"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Stitching13] <> null)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Stitching13", "Date"}, {"Stitching14", "Qty"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Department", each "Stitching")
in
#"Added Custom"
Stitching Batch 3
let
Source = #"Main Data",
#"Removed Columns" = Table.RemoveColumns(Source,{"Cutting", "Cutting9", "Stitching", "Stitching12", "Stitching13", "Stitching14", "Cutting10", "Cutting11", "Finishing", "Finishing17", "Finishing18", "Finishing19", "Rejection"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Stitching15] <> null)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Stitching15", "Date"}, {"Stitching16", "Qty"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Department", each "Stitching")
in
#"Added Custom"
Finishing Batch 1
let
Source = #"Main Data",
#"Removed Columns" = Table.RemoveColumns(Source,{"Cutting", "Cutting9", "Stitching", "Stitching12", "Stitching13", "Stitching14", "Cutting10", "Cutting11", "Stitching15", "Stitching16", "Finishing18", "Finishing19","Rejection"
}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Finishing] <> null)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Finishing", "Date"}, {"Finishing17", "Qty"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Department", each "Finishing")
in
#"Added Custom"
Finishing Batch 2
let
Source = #"Main Data",
#"Removed Columns" = Table.RemoveColumns(Source,{"Cutting", "Cutting9", "Stitching", "Stitching12", "Stitching13", "Stitching14", "Cutting10", "Cutting11", "Stitching15", "Stitching16", "Finishing", "Finishing17","Rejection"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Finishing18] <> null)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Finishing18", "Date"}, {"Finishing19", "Qty"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Department", each "Finishing")
in
#"Added Custom"
Rejections
let
Source = #"Main Data",
#"Removed Columns" = Table.RemoveColumns(Source,{"Cutting", "Cutting9", "Cutting10", "Cutting11", "Stitching", "Stitching12", "Stitching13", "Stitching14", "Stitching15", "Stitching16", "Finishing", "Finishing17", "Finishing18", "Finishing19"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Rejection] <> null)),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Department", each "Rejection"),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Date", each ""),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Rejection", "Qty"}})
in
#"Renamed Columns"
Combining all the tables again
let
Source = Table.Combine({#"Cutting Batch1", Stitching_Batch1, Stitching_Batch2, #"Cutting Batch2", #"Stitching Batch 3", #"Finishing Batch 1", #"Finishing Batch 2", Rejection}),
#"Sorted Rows" = Table.Sort(Source,{{"Order#", Order.Descending}}),
#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Order#", Int64.Type}}),
#"Sorted Rows1" = Table.Sort(#"Changed Type",{{"Order#", Order.Ascending}, {"Line#", Order.Ascending}, {"Style", Order.Ascending}, {"Date", Order.Ascending}})
in
#"Sorted Rows1"
Bookmarks