Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 2
amitchandak
Super User
Super User

@Anonymous , what is the issue you are facing. To understand M refer these links from @ImkeF .

 

 

1) M as a language

Chris Webb’s M-Webinar This is the best intro to M as a language at the moment. Recommending it as the first hour to invest in your Power Query & M-skills.
Ben Gribaudo’s series Systematic intro of M as a language
Introduction to Power Query (informally known as “M”) Formula Language Quick intro, but watch out: Strangely hidden. You can reach the content via the left navigation pane only!
Introduction to Power Query M Code Brief introduction into the M-language in Power Query
Video about M language concept Lean back and enjoy the M(agic)
Formula Language Specification Techie-hardcore: Describes the structure of the language. Quickly flip through for the first time to get an orientation. Read again after you’ve practiced for some time, with any luck it will start to speak to you
 

 

Formula Reference Bookmark this: All functions with their specs.
And actively use this!: Read the specs, copy the example codes, paste it into the formula bar or advanced editor and study the results.
M-Code Editor How to create WordPad ++ editor for M, also includes a very useful function on how to extract additional metadata for #shared-function-list.

2) Books

Power Query, Get Data, Get and Transform & M: M is for Data Monkey, Power Query for Power BI and Excel

Power Pivot & DAX: Learn to write Dax, Power Pivot and Power BI, The Definite Guide to DAX

Power BI: Introducing Power BIAnalyzing data with Power BI…

3) Online

From Microsoft: PowerBI, Power Query

 

Anonymous
Not applicable

Thanks,

we have created hana view now i want to bring that hana view fields to the power bi and create the same logic as written in below for fields plan order & additional plan orders,

now i want to unde from where this 2 fields base fields 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.