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

问题描述

我有两个桌子。第一个表称为功能：

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

第二个表称为信息组：

The second table is called Information Group:

一些 ID 已在信息组表中（即在能力 列中）引用了

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

当前，为能力视图的仪表板提供动力的查询是：

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

=来源{[Schema = dbo，Item = Capability]} [数据]

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

换句话说，由于功能 ID 10在信息组中未引用，能力表应在输出中排除该行（即仅显示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]} [数据]，功能），功能），它会生成以下内容：

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]}} [数据]，每个[ID]都包含在列表中））

我已经阅读了使用的MSDN Table。包含，但我无法弥合差距以达到想要的位置。

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.

我很满意的答案：

Answer that I am satisfied with:

let
    Source = Sql.Database("server\database", "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.Inner将 ID上的能力表与能力上的信息组表合并在一起： http://radacad.com/how-to-change-joining-types-in-power-bi-and-power-查询
，它充当过滤器。

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屋！