-
Notifications
You must be signed in to change notification settings - Fork 0
/
Part to Material
27 lines (27 loc) · 5.69 KB
/
Part to Material
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
let
Source = Troy_BOMs,
#"Merged Queries" = Table.NestedJoin(Source,{"Bill of Material Component"},Troy_BOMs,{"Material (Plant)"},"Troy_BOMs",JoinKind.LeftOuter),
#"Expanded Troy_BOMs" = Table.ExpandTableColumn(#"Merged Queries", "Troy_BOMs", {"Bill of Material", "BOM Item Number", "Bill of Material Component", "", "Quantity", "Base Quantity"}, {"Troy_BOMs1.Bill of Material", "Troy_BOMs1.BOM Item Number", "Troy_BOMs1.Bill of Material Component", "Troy_BOMs1.", "Troy_BOMs1.Quantity", "Troy_BOMs1.Base Quantity"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Troy_BOMs",{"Troy_BOMs1.Bill of Material Component"},Troy_BOMs,{"Material (Plant)"},"Troy_BOMs",JoinKind.LeftOuter),
#"Expanded Troy_BOMs1" = Table.ExpandTableColumn(#"Merged Queries1", "Troy_BOMs", {"Bill of Material", "BOM Item Number", "Bill of Material Component", "", "Quantity", "Base Quantity"}, {"Troy_BOMs2.Bill of Material", "Troy_BOMs2.BOM Item Number", "Troy_BOMs2.Bill of Material Component", "Troy_BOMs2.", "Troy_BOMs2.Quantity", "Troy_BOMs2.Base Quantity"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded Troy_BOMs1",{"Troy_BOMs2.Bill of Material Component"},Troy_BOMs,{"Material (Plant)"},"Troy_BOMs",JoinKind.LeftOuter),
#"Expanded Troy_BOMs2" = Table.ExpandTableColumn(#"Merged Queries2", "Troy_BOMs", {"Bill of Material", "BOM Item Number", "Bill of Material Component", "", "Quantity", "Base Quantity"}, {"Troy_BOMs3.Bill of Material", "Troy_BOMs3.BOM Item Number", "Troy_BOMs3.Bill of Material Component", "Troy_BOMs3.", "Troy_BOMs3.Quantity", "Troy_BOMs3.Base Quantity"}),
#"Merged Queries3" = Table.NestedJoin(#"Expanded Troy_BOMs2",{"Troy_BOMs3.Bill of Material Component"},Troy_BOMs,{"Material (Plant)"},"Troy_BOMs",JoinKind.LeftOuter),
#"Expanded Troy_BOMs3" = Table.ExpandTableColumn(#"Merged Queries3", "Troy_BOMs", {"Bill of Material", "BOM Item Number", "Bill of Material Component", "", "Quantity", "Base Quantity"}, {"Troy_BOMs4.Bill of Material", "Troy_BOMs4.BOM Item Number", "Troy_BOMs4.Bill of Material Component", "Troy_BOMs4.", "Troy_BOMs4.Quantity", "Troy_BOMs4.Base Quantity"}),
#"Grouped Rows" = Table.Group(#"Expanded Troy_BOMs3", {"Material (Plant)", "Column2"}, {{"L0 Components", each Text.Combine([Bill of Material Component],","), type text}, {"L1 Components", each Text.Combine([Troy_BOMs1.Bill of Material Component], ","), type text}, {"L2 Components", each Text.Combine([Troy_BOMs2.Bill of Material Component], ","), type text}, {"L3 Components", each Text.Combine([Troy_BOMs3.Bill of Material Component], ","), type text}, {"L4 Components", each Text.Combine([Troy_BOMs4.Bill of Material Component], ","), type text}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "AllComponents", each [L0 Components]&","&[L1 Components]&","&[L2 Components]&","&[L3 Components]&","&[L4 Components]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"AllComponents", type text}}),
#"Grouped Rows1" = Table.Group(#"Changed Type", {"Material (Plant)", "Column2"}, {{"AllComponents", each Text.Combine([AllComponents], ","), type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Grouped Rows1", {{"AllComponents", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "AllComponents"),
#"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each ([AllComponents] <> "" or null)),
#"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Material (Plant)", "AllComponents"}),
#"Merged Queries5" = Table.NestedJoin(#"Removed Duplicates",{"AllComponents"},#"Press Materials",{"Material (Plant)"},"Press Materials",JoinKind.Inner),
#"Expanded Press Materials" = Table.ExpandTableColumn(#"Merged Queries5", "Press Materials", {"Column2", "Old material number"}, {"Press Materials.Column2", "Press Materials.Old material number"}),
#"Grouped Rows3" = Table.Group(#"Expanded Press Materials", {"Material (Plant)", "Column2"}, {{"AllComponents", each Text.Combine([AllComponents], ","), type text}, {"Descriptions", each Text.Combine([Press Materials.Column2], ", "), type text}, {"OldNums", each Text.Combine([Press Materials.Old material number], ", "), type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Grouped Rows3", "Press Material", each if Text.Contains([AllComponents], "0049388319") then "0049388319" else if Text.Contains([AllComponents], "0049388613") then "0049388613" else if Text.Contains([AllComponents], "0049388320") then "0049388320" else if Text.Contains([AllComponents], "0049388321") then "0049388321" else if Text.Contains([AllComponents], "0070604419") then "0070604419" else if Text.Contains([AllComponents], "0049388595") then "0049388595" else if Text.Contains([AllComponents], "0070604184") then "0070604184" else if Text.Contains([AllComponents], "0090005480") then "0090005480" else if Text.Contains([AllComponents], "0070600024") then "0070600024" else if Text.Contains([AllComponents], "0049385976") then "0049385976" else if Text.Contains([AllComponents], "0070600047") then "0070600047" else if Text.Contains([AllComponents], "0070600049") then "0070600049" else if Text.Contains([AllComponents], "0070600065") then "0070600065" else [AllComponents]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Press Material", type text}}),
#"Merged Queries4" = Table.NestedJoin(#"Changed Type1",{"Press Material"},MM01_Data,{"Material (Plant)"},"MM01_Data",JoinKind.LeftOuter),
#"Expanded MM01_Data" = Table.ExpandTableColumn(#"Merged Queries4", "MM01_Data", {"Column2", "Old material number"}, {"MM01_Data.Column2", "MM01_Data.Old material number"}),
FinalTable = #"Expanded MM01_Data"
in
FinalTable