excel 如何将两列与两个不同的表合并而不会在结果列中出现重复项

q8l4jmvw 于 2023-03-20 发布在其他

关注(0)|答案(1)|浏览(144)

我有三张table。
表一

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

表二

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

以及表3，其具有来自旧系统和新系统的相应对应物。

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

为了显示和管理两种系统的差异，我需要提供以下最终数据，

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

目前，我在excel中使用多个过滤函数、Vstack和Hstack进行此操作，但我无法将此解决方案扩展到数百个包。我尝试在Power BI中进行此操作，但在合并和扩展旧包组合后，与新组合（具有新包名称）合并会产生太多额外行。我需要Power Query或Dax中的解决方案。
目前在Power Query中获得如下结果。

有什么办法可以解决这个问题吗？

excel

来源：https://stackoverflow.com/questions/75774634/how-to-merge-the-two-columns-with-two-different-tables-without-getting-duplicate

1条答案

按热度按时间

zhte4eai1#

这似乎行得通：

假设您的表被读入三个单独的查询**可以将它们全部读入一个查询，但如果您不习惯直接将代码写入高级编辑器，则此方法更简单。*

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"

赞(0）回复(0）举报 2023-03-20

我来回答

excel 如何将两列与两个不同的表合并而不会在结果列中出现重复项

1条答案

相关问题

热门标签

最新问答