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

Question

这是我第一次在stackoverflow上提问，让我们看看这是怎么回事！

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

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

场景管理器：

设想	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 中的数据模型中，并将场景管理器中的 SG 和 HOB 作为 Config 未透视，然后将 ScenarioManager 中的 Config 连接到 CostSheet 中的 Config。但是，这样我就无法以我想要的方式查看数据透视表中的数据。为了实现我需要的视图，我需要创建第二个 CostSheet，其中 Years 列也是不可透视的。但是，当我这样前进时，数据透视表不会向我显示正确的值，而是显示特定年份的所有配置的总和。

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

过滤器：场景 1

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

等等。对于我所拥有的每一个场景。

我在这里上传了带有数据的原始文件

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

最好的朱莉娅

标签： excelpivotpowerquerypowerpivotdatamodel

Answer 1

在 Power Query 中，取消透视ScenarioManager表后，可以将其与列CostSheet上的表合并Config。这是 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。