显示 PowerBI 中存在外键的行 [英] Display rows where foreign key is present in PowerBI

问题描述

我有两张桌子.第一个表称为能力:

I have two tables. The first table is called Capability:

第二个表称为信息组:

Capability 表中的一些 ID 已在 Information Group 表中被引用(即在 Capability 列中)

Some ID's in the Capability table have been referenced in the Information Group table (i.e. in the Capability column)

目前，为 Capability 视图提供仪表板的查询是:

At present, the query that powers the dashboard for the Capability view is:

= Source{[Schema="dbo",Item="Capability"]}[Data]

我只想在功能表中显示信息组表中至少有一个引用的行.

换句话说，由于信息组中未引用 Capability ID 10，因此 Capability 表应在输出中排除该行(即仅显示 1 到 9).

In other words, since Capability ID 10 is not referenced in Information Group, the Capability table should exclude that row in the output (i.e. shows only 1 to 9).

|------------------------------------------------------------------|

|------------------------------------------------------------------|

到目前为止所做的努力:

Effort made so far:

我已设法通过此查询在信息组表中选择不同的功能 ID:= Table.SelectColumns(Table.Distinct(Source{[Schema="dbo",Item="Information_Group"]}[Data], "Capability"), "Capability")，它产生这个:

I have managed to select distinct Capability ID's in the Information Group table via this query: = Table.SelectColumns(Table.Distinct(Source{[Schema="dbo",Item="Information_Group"]}[Data], "Capability"), "Capability"), which produces this:

我想做的下一步是根据上面的列表检查 Capability 表的 ID 列，并仅显示表中包含值的行.像这样的:

The next step that I would like to do is to check the ID column of the Capability table against the list above and display only rows where the values are contained in the table. Something like this:

= Table.SelectRows(Table.Contains(Source{[Schema="dbo",Item="Capability"]}[Data], each[ID] 包含在列表中))

我已经阅读了有关使用 Table.Contains 的 MSDN，但我无法弥合差距以到达我想要的位置.

I have read up MSDN on using Table.Contains, but I cannot bridge the gap to get to where I want.

如果需要澄清，请告诉我.谢谢.

Please let me know if clarification is needed. Thank you.

我满意的答案:

let
    Source = Sql.Database("serverdatabase", "Mclaren"),
    dbo_Capability = Table.NestedJoin(
        Source{[Schema="dbo",Item="Capability"]}[Data],{"ID"},
        Source{[Schema="dbo",Item="Information_Group"]}[Data],{"Capability"},        
        "NewColumn",
        JoinKind.RightOuter
    ),
    #"Removed Columns" = Table.RemoveColumns(dbo_Capability,{"NewColumn"})
in
    #"Removed Columns"

推荐答案

您只需使用 JoinKind 将ID"上的能力"表与能力"上的信息组"表合并.内部:http://radacad.com/how-to-change-joining-types-in-power-bi-and-power-query这充当过滤器.

You simply have to perform a merge of your "Capability"-table on "ID" with the "Information Group"-table on "Capability" with JoinKind.Inner: http://radacad.com/how-to-change-joining-types-in-power-bi-and-power-query This acts as a filter.

这篇关于显示 PowerBI 中存在外键的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！