excel - 具有重复项的表之间的关系 (Excel)

问题描述

我正在尝试加入两个都有（并且需要有）重复的数据集。

这是两个表的虚拟数据的示例：

t.componentsneeded
ORDER    PRODUCT   PENDINGSALES    components    component_stock    
 1        REF1         100           COMP1            50              
 1        REF1         100           COMP2            100            
 1        REF1         100           COMP3            50             
 2        REF1         200           COMP1            50              
 2        REF1         200           COMP2            100             
 2        REF1         200           COMP3            50

我想把第一张桌子和另一张桌子一起加入：

t.pendingtoarrive
ORDER DATE    ARRIVAL DATE    PURCHASE_ORDER   COMPONENT     UNITS
01/11/2020    01/12/2020          1              COMP1        100
15/11/2020    15/12/2020          2              COMP1        100
01/12/2020    01/01/2021          3              COMP1        50

第一个数据集在如下表中进行透视：

FILTER ORDER: ALL                               (COLUMN I WOULD JOIN)
COMPONENT       NEEDED        STOCK     TO_BUY      TO_ARRIVE
COMP1            300           50         250           250
COMP2            300           100        200            -
COMP3            300           50         250            -

我试图创建一个没有重复值（COMPONENT）的新表来连接两个表。但是然后数据透视表汇总了总单位数，t.pendingtoarrive而没有从t.componentsneeded计数中获取过滤器（唯一要记住的过滤器是“组件”）。我想这样做，以便最终用户可以双击 TO_ARRIVE 中的值，它会打开包含数据的表。如果有任何其他方法可以实现相同的目标......

谢谢你。任何帮助将不胜感激

标签： excelpivot-tablepowerpivot

解决方案

您可以使用 Excel 2010+ 中提供的 Power Query 执行此操作您执行Nested Join并提取适当的值

确保您的两个数据集设置为Tables
- 记录表名
通过以下任一方式打开 PQ 编辑器
- 打开一个空白查询或
- 在数据集 1 中选择一个单元格，然后Data => Get & Transform => from Table/Range
Home/Advanced Editor并将下面的代码粘贴到编辑器中
- 更改相应行中的表名以反映您的实际表名
- 检查 Applied Steps 窗口和代码，看看它是如何完成的。

M代码

let
    //Get data set 1
    Source = Excel.CurrentWorkbook(){[Name="needed"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ORDER", Int64.Type}, {"PRODUCT", type text}, {"PENDINGSALES", Int64.Type}, {"components", type text}, {"component_stock", Int64.Type}}),

    //Group by component to generate "pivot"
    //Note that "needed" is a SUM, and "stock" is an AVERAGE
    #"Grouped Rows" = Table.Group(#"Changed Type", {"components"}, {
            {"NEEDED", each List.Sum([PENDINGSALES]), type nullable number}, 
            {"STOCK", each List.Average([component_stock]), type nullable number}
            }),

    // Generate the "to buy" column
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "TO_BUY", each [NEEDED]-[STOCK]),

    //Get data set 2
    Ordered = Excel.CurrentWorkbook(){[Name="pendingArrive"]}[Content],
    #"Changed Type1" = Table.TransformColumnTypes(Ordered,{{"ORDER DATE", type date}, {"ARRIVAL DATE", type date}}),

    //Join with set 1
    join = Table.NestedJoin(#"Added Custom","components",#"Changed Type1","COMPONENT","Joined"),

    //extract total for each componennt
    #"Added Custom1" = Table.AddColumn(join, "TO_ARRIVE", each List.Sum(Table.Column([Joined],"UNITS"))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Joined"})
in
    #"Removed Columns"

excel - 具有重复项的表之间的关系 (Excel)

问题描述

解决方案

推荐阅读