如何从PowerQuery/Excel数据模型中具有多对多关系的两个表中挑选数据？ [英] How do I pick data from two tables with many to many relationships in PowerQuery / Excel Data Model?

问题描述

这是我第一次问有关堆栈溢出的问题，让我们看看情况如何！

我正在尝试将不同大小资产的方案管理器连接到它们所属的成本时间序列，以便我可以计算属于某个方案的资产配置的现金流。

这是(简写)我的两个需要连接的表：

场景管理器：

方案	SG	滚刀
场景1	SG280	HB200
场景2	SG320	HOB160

成本单：

配置	子类型	2021	2022	2023	2024
SG280	SG	-500	-180	-250	-680
SG320	SG	-600	-700	-345	-880
HOB200	滚刀	-300	-680	-500	-320
HOB160	滚刀	-250	-300	-260	-700

我将数据加载到Excel中的数据模型中，并将Scenario Manager中的SG和HOB取消透视为Config，然后将Config从ScenarioManager连接到CostSheet中的Config。然而，这样我就不能以我想要的方式查看数据透视表中的数据。为了实现我需要的视图，我需要创建第二个CostSheet，其中的年份列也是取消透视的。然而，当我以这种方式前进时，数据透视表并没有向我显示正确的值，而是显示了特定年份的所有配置的总和。

我要创建的表应该如下所示：

滤镜：场景1

配置	子类型	2021	2022	2023	2024
SG280	SG	-500	-180	-250	-680
HOB200	滚刀	-300	-680	-500	-320

我的每个方案都是如此。

I uploaded the original file with the data here

希望你们能帮助我，提前谢谢你们！

最佳 朱莉娅

推荐答案

在Power Query中，取消透视ScenarioManager表后，即可将其与Config列上的CostSheet表合并。以下是Power Query M代码，其中ScenarioManager和CostSheet是导入上面给出的示例表的查询：

let
    Source = ScenarioManager,
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Scenario"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Value"}, CostSheet, {"Config"}, "CostSheet", JoinKind.LeftOuter),
    #"Expanded CostSheet" = Table.ExpandTableColumn(#"Merged Queries", "CostSheet", {"Subtype", "2021", "2022", "2023", "2024"}, {"Subtype", "2021", "2022", "2023", "2024"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded CostSheet",{{"Value", "Config"}})
in
    #"Renamed Columns"

然后您可以将此查询作为表加载，并使用Scenario列对其进行筛选。

如果您喜欢使用数据透视表，您可以添加一个步骤来取消透视表的透视，然后将其加载到数据透视表中：

    ...
    #"Unpivoted Columns2" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Scenario", "Config", "Subtype"}, "Year", "Value")
in
    #"Unpivoted Columns2"

在将其应用于整个数据集时，默认情况下可能会按计数聚合这些值。由于每个Config-Year对只有一个要求和的值，因此按求和将产生所需的结果。

这篇关于如何从PowerQuery/Excel数据模型中具有多对多关系的两个表中挑选数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！