Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Please help me in understanding below code for(plan Order & additional Plan orders)


Source = Table.Combine({#"RAW DEMAND FORECAST (ALL ONE MONTH)", #"RAW DEMAND FORECAST (FIRST MONTH PARTIALS)", #"RAW DEMAND FORECAST (SECOND MONTH PARTIALS)"}),
#"Removed Columns" = Table.RemoveColumns(Source,{"Start Date Plus Six", "Same Month"}),
#"Added Custom22" = Table.AddColumn(#"Removed Columns", "Indicator", each "FERT"),
#"Appended Query1" = Table.Combine({#"Added Custom22", #"DEPENDENT DEMAND (ALL ONE MONTH)"}),
#"Added Custom" = Table.AddColumn(#"Appended Query1", "First Day", each Number.ToText(Date.Month([STARTDATE]))&" "&Number.ToText(Date.Year([STARTDATE]))),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"First Day", type date}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"STARTDATE", type date}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"First Day", "SCHEDDATE"}}),
#"Renamed Columns" = Table.RenameColumns(#"Renamed Columns1",{{"TOTFCST_LBS", "Demand Forecast"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"ITEM", "RESOURCE_WC", "PROFITCENTER", "UDC_BUSINESS_UNIT", "LOC", "SCHEDDATE", "PLAN_MODE", "Indicator"}, {{"DEMAND", each List.Sum([Demand Forecast]), type number}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"ITEM", "LOC", "SCHEDDATE"}, #"SCHEDRCPTS PRODUCTION", {"ITEM", "LOC", "SCHEDDATE"}, "SCHEDRCPTS PRODUCTION", JoinKind.FullOuter),
#"Expanded SCHEDRCPTS PRODUCTION1" = Table.ExpandTableColumn(#"Merged Queries", "SCHEDRCPTS PRODUCTION", {"UDC_BUSINESS_UNIT", "PROFITCENTER", "RESOURCE_WC", "ITEM", "ITEM_DESCRIPTION", "LOC", "LOCATION_DESCRIPTION", "LINE", "ITEM_WGT", "ITEM_UOM", "PLAN_MODE", "SCHEDDATE", "SourceFrom", "UID_SRCPTS", "Sched Production"}, {"UDC_BUSINESS_UNIT.1", "PROFITCENTER.1", "RESOURCE_WC.1", "ITEM.1", "ITEM_DESCRIPTION", "LOC.1", "LOCATION_DESCRIPTION", "LINE", "ITEM_WGT", "ITEM_UOM", "PLAN_MODE.1", "SCHEDDATE.1", "SourceFrom", "UID_SRCPTS", "Sched Production"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded SCHEDRCPTS PRODUCTION1",{{"Sched Production", "SCHED RCPTS"}}),
#"Merged Queries1" = Table.NestedJoin(#"Renamed Columns2", {"ITEM", "LOC", "SCHEDDATE"}, #"PLAN ORDER PRODUCTION", {"ITEM", "LOC", "SCHEDDATE"}, "PLAN ORDER PRODUCTION", JoinKind.FullOuter),
#"Expanded PLAN ORDER PRODUCTION1" = Table.ExpandTableColumn(#"Merged Queries1", "PLAN ORDER PRODUCTION", {"UDC_BUSINESS_UNIT", "PROFITCENTER", "RESOURCE_WC", "ITEM", "ITEM_NAME", "LOC", "LOC_NAME", "PLAN_MODE", "SCHEDDATE", "SourceFrom", "QTY", "PROD_QTY_LBS", "MATTYPE"}, {"UDC_BUSINESS_UNIT.2", "PROFITCENTER.2", "RESOURCE_WC.2", "ITEM.2", "ITEM_NAME", "LOC.2", "LOC_NAME", "PLAN_MODE.2", "SCHEDDATE.2", "SourceFrom.1", "QTY", "PROD_QTY_LBS", "MATTYPE"}),
#"Renamed Columns3" = Table.RenameColumns(#"Expanded PLAN ORDER PRODUCTION1",{{"PROD_QTY_LBS", "PLAN ORDER"}}),
#"Merged Queries2" = Table.NestedJoin(#"Renamed Columns3", {"ITEM", "LOC", "SCHEDDATE"}, #"RES LOAD DETAIL (ITEM)", {"ITEM", "LOC", "SCHEDDATE"}, "RES LOAD DETAIL (ITEM)", JoinKind.FullOuter),
#"Expanded RES LOAD DETAIL (ITEM)1" = Table.ExpandTableColumn(#"Merged Queries2", "RES LOAD DETAIL (ITEM)", {"RES", "ITEM", "LOC", "SCHEDDATE", "LOADQTY", "PRODRATE", "PLAN_MODE", "PRODRATE_LBS_PER_HOUR", "HRS", "RES LOAD DETAIL (RESOURCE).HRS", "PERCENT RESOURCE ALLOCATION", "AVAIL_HRS", "CAPACITY"}, {"RES", "ITEM.3", "LOC.3", "SCHEDDATE.3", "LOADQTY", "PRODRATE", "PLAN_MODE.3", "PRODRATE_LBS_PER_HOUR", "HRS", "RES LOAD DETAIL (RESOURCE).HRS", "PERCENT RESOURCE ALLOCATION", "AVAIL_HRS", "CAPACITY"}),
#"Merged Queries4" = Table.NestedJoin(#"Expanded RES LOAD DETAIL (ITEM)1", {"ITEM", "LOC", "SCHEDDATE"}, #"CURRENT OH INVENTORY", {"ITEM", "LOC", "SCHEDDATE"}, "CURRENT OH INVENTORY", JoinKind.FullOuter),
#"Expanded CURRENT OH INVENTORY1" = Table.ExpandTableColumn(#"Merged Queries4", "CURRENT OH INVENTORY", {"ITEM", "LOC", "PROFITCENTER", "UDC_BUSINESS_UNIT", "RESOURCE_WC", "PLAN_MODE", "OH", "WGT", "CURRENTOH_TOTAL_LBS", "SourceFrom", "SCHEDDATE"}, {"ITEM.4", "LOC.4", "PROFITCENTER.3", "UDC_BUSINESS_UNIT.3", "RESOURCE_WC.3", "PLAN_MODE.4", "OH", "WGT", "CURRENTOH_TOTAL_LBS", "SourceFrom.2", "SCHEDDATE.4"}),
#"Merged Queries3" = Table.NestedJoin(#"Expanded CURRENT OH INVENTORY1", {"ITEM", "LOC", "SCHEDDATE"}, #"FULL SUPPLY UPLOAD", {"ITEM", "LOC", "SCHEDDATE"}, "FULL SUPPLY UPLOAD", JoinKind.LeftOuter),
#"Expanded FULL SUPPLY UPLOAD" = Table.ExpandTableColumn(#"Merged Queries3", "FULL SUPPLY UPLOAD", {"PLAN ORDER", "CAPACITY", "ADDITIONAL INVENTORY"}, {"ADDITIONAL PLAN ORDERS", "ADDITIONAL CAPACITY", "ADDITIONAL INVENTORY"}),
#"Appended Query" = Table.Combine({#"Expanded FULL SUPPLY UPLOAD", #"NEW ITEMS"}),
#"Renamed Columns4" = Table.RenameColumns(#"Appended Query",{{"CURRENTOH_TOTAL_LBS", "CURRENT OH INVENTORY"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns4",null,0,Replacer.ReplaceValue,{"SCHED RCPTS"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"PLAN ORDER"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",null,0,Replacer.ReplaceValue,{"CAPACITY"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",null,0,Replacer.ReplaceValue,{"CURRENT OH INVENTORY"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3",null,0,Replacer.ReplaceValue,{"ADDITIONAL PLAN ORDERS"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4",null,0,Replacer.ReplaceValue,{"ADDITIONAL CAPACITY"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5",null,0,Replacer.ReplaceValue,{"ADDITIONAL INVENTORY"}),
#"Added Custom2" = Table.AddColumn(#"Replaced Value6", "TOTAL PLAN ORDERS", each [PLAN ORDER] + [ADDITIONAL PLAN ORDERS]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"TOTAL PLAN ORDERS", type number}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type2", "TOTAL CAPACITY", each [CAPACITY] + [ADDITIONAL CAPACITY]),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom3",{{"TOTAL CAPACITY", type number}}),
#"Added Custom4" = Table.AddColumn(#"Changed Type3", "CONSTRAINED PRODUCTION", each if [TOTAL CAPACITY] < [TOTAL PLAN ORDERS] then [TOTAL CAPACITY] else [TOTAL PLAN ORDERS]),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom4",{{"CONSTRAINED PRODUCTION", type number}}),
#"Added Custom5" = Table.AddColumn(#"Changed Type4", "ITEM ID", each if [ITEM] <> null then [ITEM] else if [ITEM.1] <> null then [ITEM.1] else if [ITEM.2] <> null then [ITEM.2] else if [ITEM.3] <> null then [ITEM.3] else [ITEM.4]),
#"Changed Type5" = Table.TransformColumnTypes(#"Added Custom5",{{"ITEM ID", type text}}),
#"Replaced Value7" = Table.ReplaceValue(#"Changed Type5"," ",null,Replacer.ReplaceValue,{"RESOURCE_WC"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7"," ",null,Replacer.ReplaceValue,{"RESOURCE_WC.1"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8"," ",null,Replacer.ReplaceValue,{"RESOURCE_WC.2"}),
#"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9"," ",null,Replacer.ReplaceValue,{"RESOURCE_WC.3"}),
#"Added Custom6" = Table.AddColumn(#"Replaced Value10", "RESOURCE WORK CENTER", each if [RESOURCE_WC] <> null then [RESOURCE_WC] else if [RESOURCE_WC.1] <> null then [RESOURCE_WC.1] else if [RESOURCE_WC.2] <> null then [RESOURCE_WC.2] else [RESOURCE_WC.3]),
#"Changed Type6" = Table.TransformColumnTypes(#"Added Custom6",{{"RESOURCE WORK CENTER", type text}, {"PROFITCENTER", type text}}),
#"Replaced Value11" = Table.ReplaceValue(#"Changed Type6","",null,Replacer.ReplaceValue,{"PROFITCENTER"}),
#"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11","",null,Replacer.ReplaceValue,{"PROFITCENTER.1"}),
#"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12","",null,Replacer.ReplaceValue,{"PROFITCENTER.2"}),
#"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13","",null,Replacer.ReplaceValue,{"PROFITCENTER.3"}),
#"Added Custom7" = Table.AddColumn(#"Replaced Value14", "PROFIT CENT", each if [PROFITCENTER] <> null then [PROFITCENTER] else if [PROFITCENTER.1] <> null then [PROFITCENTER.1] else if[PROFITCENTER.2] <> null then [PROFITCENTER.2] else [PROFITCENTER.3]),
#"Changed Type7" = Table.TransformColumnTypes(#"Added Custom7",{{"PROFIT CENT", type text}}),
#"Replaced Value15" = Table.ReplaceValue(#"Changed Type7","",null,Replacer.ReplaceValue,{"UDC_BUSINESS_UNIT"}),
#"Replaced Value16" = Table.ReplaceValue(#"Replaced Value15","",null,Replacer.ReplaceValue,{"UDC_BUSINESS_UNIT.1"}),
#"Replaced Value17" = Table.ReplaceValue(#"Replaced Value16","",null,Replacer.ReplaceValue,{"UDC_BUSINESS_UNIT.2"}),
#"Replaced Value18" = Table.ReplaceValue(#"Replaced Value17","",null,Replacer.ReplaceValue,{"UDC_BUSINESS_UNIT.3"}),
#"Added Custom8" = Table.AddColumn(#"Replaced Value18", "BUSINESS UNIT", each if [UDC_BUSINESS_UNIT] <> null then [UDC_BUSINESS_UNIT] else if [UDC_BUSINESS_UNIT.1] <> null then [UDC_BUSINESS_UNIT.1] else if [UDC_BUSINESS_UNIT.2] <> null then [UDC_BUSINESS_UNIT.2] else [UDC_BUSINESS_UNIT.3]),
#"Changed Type8" = Table.TransformColumnTypes(#"Added Custom8",{{"BUSINESS UNIT", type text}}),
#"Replaced Value19" = Table.ReplaceValue(#"Changed Type8","",null,Replacer.ReplaceValue,{"LOC"}),
#"Replaced Value20" = Table.ReplaceValue(#"Replaced Value19","",null,Replacer.ReplaceValue,{"LOC.1"}),
#"Replaced Value21" = Table.ReplaceValue(#"Replaced Value20","",null,Replacer.ReplaceValue,{"LOC.2"}),
#"Replaced Value22" = Table.ReplaceValue(#"Replaced Value21","",null,Replacer.ReplaceValue,{"LOC.3"}),
#"Replaced Value23" = Table.ReplaceValue(#"Replaced Value22","",null,Replacer.ReplaceValue,{"LOC.4"}),
#"Added Custom9" = Table.AddColumn(#"Replaced Value23", "LOCATION", each if [LOC] <> null then [LOC] else if [LOC.1] <> null then [LOC.1] else if [LOC.2] <> null then [LOC.2] else if [LOC.3] <> null then [LOC.3] else [LOC.4]),
#"Changed Type9" = Table.TransformColumnTypes(#"Added Custom9",{{"LOCATION", type text}, {"PLAN_MODE", type text}}),
#"Replaced Value24" = Table.ReplaceValue(#"Changed Type9","",null,Replacer.ReplaceValue,{"PLAN_MODE"}),
#"Replaced Value25" = Table.ReplaceValue(#"Replaced Value24","",null,Replacer.ReplaceValue,{"PLAN_MODE.1"}),
#"Replaced Value26" = Table.ReplaceValue(#"Replaced Value25","",null,Replacer.ReplaceValue,{"PLAN_MODE.2"}),
#"Replaced Value27" = Table.ReplaceValue(#"Replaced Value26","",null,Replacer.ReplaceValue,{"PLAN_MODE.3"}),
#"Replaced Value28" = Table.ReplaceValue(#"Replaced Value27","",null,Replacer.ReplaceValue,{"PLAN_MODE.4"}),
#"Added Custom10" = Table.AddColumn(#"Replaced Value28", "PLN MODE", each if [PLAN_MODE] <> null then [PLAN_MODE] else if [PLAN_MODE.1] <> null then [PLAN_MODE.1] else if [PLAN_MODE.2] <> null then [PLAN_MODE.2] else if [PLAN_MODE.3] <> null then [PLAN_MODE.3] else [PLAN_MODE.4]),
#"Changed Type10" = Table.TransformColumnTypes(#"Added Custom10",{{"PLN MODE", type text}}),
#"Added Custom11" = Table.AddColumn(#"Changed Type10", "DATE", each if [SCHEDDATE] <> null then [SCHEDDATE] else if [SCHEDDATE.1] <> null then [SCHEDDATE.1] else if [SCHEDDATE.2] <> null then [SCHEDDATE.2] else if [SCHEDDATE.3] <> null then [SCHEDDATE.3] else [SCHEDDATE.4]),
#"Changed Type11" = Table.TransformColumnTypes(#"Added Custom11",{{"DATE", type date}}),
#"Added Custom12" = Table.AddColumn(#"Changed Type11", "PLAN ORDER OVER CAPACITY", each if [TOTAL PLAN ORDERS] > [TOTAL CAPACITY] then [TOTAL PLAN ORDERS] - [TOTAL CAPACITY] else 0),
#"Changed Type12" = Table.TransformColumnTypes(#"Added Custom12",{{"PLAN ORDER OVER CAPACITY", type number}}),
#"Replaced Value29" = Table.ReplaceValue(#"Changed Type12",null,0,Replacer.ReplaceValue,{"DEMAND"}),
#"Added Custom13" = Table.AddColumn(#"Replaced Value29", "DEMAND OVER PRODUCTION", each if [DEMAND] > ([TOTAL PLAN ORDERS] + [SCHED RCPTS]) then [DEMAND] - ([TOTAL PLAN ORDERS] + [SCHED RCPTS]) else 0),
#"Changed Type13" = Table.TransformColumnTypes(#"Added Custom13",{{"DEMAND OVER PRODUCTION", type number}}),
#"Added Custom14" = Table.AddColumn(#"Changed Type13", "UNIQUE IDENTIFIER", each [ITEM ID] & [LOCATION] & Date.ToText([DATE])),
#"Changed Type14" = Table.TransformColumnTypes(#"Added Custom14",{{"UNIQUE IDENTIFIER", type text}}),
#"Added Custom17" = Table.AddColumn(#"Changed Type14", "DATE BU PROFITCENTER", each Date.ToText([DATE])&[BUSINESS UNIT]&[PROFIT CENT]),
#"Added Custom18" = Table.AddColumn(#"Added Custom17", "TIMESTAMP", each DateTime.LocalNow()),
#"Changed Type17" = Table.TransformColumnTypes(#"Added Custom18",{{"TIMESTAMP", type date}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type17", each [DATE] <> null and [DATE] <> ""),
#"Added Custom19" = Table.AddColumn(#"Filtered Rows1", "AFTER THIS MONTH", each if [DATE] > [TIMESTAMP] then true else false),
#"Filtered Rows" = Table.SelectRows(#"Added Custom19", each ([AFTER THIS MONTH] = true)),
#"Added Custom21" = Table.AddColumn(#"Filtered Rows", "PC BU", each [PROFITCENTER]&[UDC_BUSINESS_UNIT])
in
Added Custom21

2 Replies