How to merge the Two Columns with two different Tables without getting duplicates in either of the resulting columns

Question

I have 3 tables.

Table One

Old Pack Name   Composition
Pack 1          ABC
Pack 1          BCD
Pack 1          CDE
Pack 1          DEF
Pack 2          ABC
Pack 2          CDE
Pack 2          DEF
Pack W          ABC
Pack W          BCD
Pack W          CDE
Pack W          DEF
Pack W          XZE

Table 2

New Pack Name   Composition
New Pack A          ABC
New Pack A          BCD
New Pack A          CDE
New Pack D          ABC
New Pack D          CDE
New Pack D          DEF
New Pack D          GQH
New Pack 1          ABC
New Pack 1          BCD
New Pack 1          CDE
New Pack 1          XZE
New Pack 1          WER
New Pack 1          POU

and Table 3, Which has the respective counterparts from old and new systems.

Old Pack Name   New Pack Name
Pack 1          New Pack A
Pack 2          New Pack D
Pack W          New Pack 1

In order to Present and manage the differences in both the systems I need to present the final data as below,

Old Pack Name   Old Composition Status  New Pack Name   New Composition STATUS
Pack 1          ABC         KEPT            New Pack A          ABC          --
Pack 1          BCD         KEPT            New Pack A          BCD          --
Pack 1          CDE         KEPT            New Pack A          CDE          --
Pack 1          DEF         REMOVED                                 
Pack 2          ABC         KEPT            New Pack D          ABC          --
Pack 2          CDE         KEPT            New Pack D          CDE          --
Pack 2          DEF         KEPT            New Pack D          DEF          --
                                            New Pack D          GQH         NEW
Pack W          ABC         KEPT            New Pack 1          ABC          --
Pack W          BCD         KEPT            New Pack 1          BCD          --
Pack W          CDE         KEPT            New Pack 1          CDE          --
Pack W          DEF         REMOVED         New Pack 1          XZE          --
Pack W          XZE         KEPT            New Pack 1          WER         NEW
                                            New Pack 1          POU         NEW

I'm trying to scale this method for hundreds of Packs right now using Excel, several filter functions, Vstack, and Hstack. I'm trying to accomplish it with Power BI, but merging with the new composition (with the new pack name) after merging and expanding the old pack composition results in way too many more rows. I require a Power Query or Dax solution.

Currently getting the results like below in Power Query.

Answer 1

Assume your tables as named are read into three separate queries Could read them all into a single query, but this method is simpler if you are not used to writing code directly into the Advanced Editor.

let

//Merge Tables 1 and 3
    Source = Table.NestedJoin(Table_1, {"Old Pack Name"}, Table_3, {"Old Pack Name"}, "Table_3", JoinKind.FullOuter),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each [Table_3][New Pack Name]{0}, type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Table_3"}),

//Add Index column for sorting the end results
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),

//Merge above with Table 2
//then remove unneeded columns
    #"Merged Queries" = Table.NestedJoin(#"Added Index", 
        {"Custom", "Composition"}, Table_2, {"New Pack Name", "Composition"}, "Table_2", JoinKind.FullOuter),
    #"Removed Columns1" = Table.RemoveColumns(#"Merged Queries",{"Custom"}),

//Expand and rename the merged table columns
    #"Expanded Table_2" = Table.ExpandTableColumn(#"Removed Columns1", "Table_2", {"New Pack Name", "Composition"}, {"New Pack Name", "New Composition"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Table_2",{{"Composition", "Old Composition"}}),

//add custom columns for STATUS and "Composition Status"
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "STATUS", 
        each if [Old Pack Name] = null then "NEW" else null, type text),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Composition Status", 
        each if [New Pack Name] = null then "REMOVED" else if [Old Pack Name] <> null then "KEPT" else null, type text),

//set proper column order
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom2",
        {"Old Pack Name", "Old Composition", "Composition Status", "New Pack Name", "New Composition", "STATUS"}),

//Fill Down Index column for sorting
// then sort into desired order
    #"Filled Down" = Table.FillDown(#"Reordered Columns",{"Index"}),
    #"Sorted Rows" = Table.Sort(#"Filled Down",{{"Index", Order.Ascending}, {"STATUS", Order.Ascending}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns2"