如何从 PowerQuery/Excel 数据模型中具有多对多关系的两个表中选取数据？

这是我第一次在 stackoverflow 上提问，让我们看看进展如何！

我正在尝试将不同规模资产的场景管理器连接到其所属的成本时间序列，以便我可以计算属于特定场景的资产配置的现金流。

这就是我需要连接的两个表（简而言之）的样子：

场景管理器：

成本表：

我将数据加载到 Excel 中的数据模型，并在场景管理器中将 SG 和 HOB 作为配置取消透视，然后将配置从场景管理器连接到 CostSheet 中的配置。但是，这样我就无法按照我想要的方式查看数据透视表中的数据。为了实现我需要的视图，我需要创建第二个 CostSheet，其中 Years 列也未透视。然而，当我像这样前进时，数据透视表不会向我显示正确的值，而是显示特定年份所有配置的总和。

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

过滤器：场景1

对于我遇到的每个场景等等。

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

最好的 朱莉娅

在 Power Query 中，一旦取消透视ScenarioManager表，您可以将其与CostSheet表上的Config柱子。这是 Power Query M 代码，其中ScenarioManager and 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 column.

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

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

将其应用于完整数据集时，默认情况下可能会按计数聚合这些值。按总和聚合将产生所需的结果，因为每个值只有单个值可以求和Config-Year pair.