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

0 votes

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.

enter image description here

Mar 19, 2023 in Others by narikkadan
• 63,420 points
357 views

1 answer to this question.

0 votes

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"

enter image description here

enter image description here

answered Mar 19, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

How to merge two cells in excel with same field name

Insert 2 new columns, G & H. Enter ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,420 points
1,157 views
0 votes
1 answer

Excel: How to merge two columns into one (from different sheets or separated columns)

This equation is completely adjustable. Your two ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,420 points
528 views
0 votes
1 answer

How to merge two Tables and Add the amount at the same time?

Use SUMPRODUCT like this: =SUMPRODUCT(($A$2:$A$5=A10)*$B$2:$B$5)+ ...READ MORE

answered Jan 7, 2023 in Others by narikkadan
• 63,420 points
265 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
3,788 views
0 votes
1 answer

Power Query : http POST request with form data

Try using Uri.BuildQueryString and Json.Document. let ...READ MORE

answered Dec 12, 2018 in Power BI by Upasana
• 8,620 points
8,287 views
0 votes
1 answer

Using Treemap as a filter

What you want is possible using the ...READ MORE

answered Dec 17, 2018 in Power BI by Shubham
• 13,490 points
4,107 views
0 votes
1 answer

Bar chart : Arranging in descending order using data from another chart

Select treemap.  Make it active.  Turn on Visual Interactions ...READ MORE

answered Feb 8, 2019 in Power BI by Upasana
• 8,620 points
463 views
0 votes
1 answer

How to compare 2 cells with delimited items in each and output the difference in items?

The following function would do this for ...READ MORE

answered Feb 23, 2023 in Others by Kithuzzz
• 38,010 points
314 views
0 votes
1 answer

How to use COUNTIFS with multiple conditions and columns in Excel?

Use  SUMPRODUCT and double unay operators: =SUMPRODUCT ...READ MORE

answered Apr 4, 2023 in Others by Kithuzzz
• 38,010 points
292 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP