Power Query - 在两个表中添加列 CountIF 即 =COUNTIF(People[CityId],[@Id]) [英] Power Query - Add Column CountIF across two tables ie =COUNTIF(People[CityId],[@Id])

问题描述

我在 PowerQuery City 和 People 中有两个表.

I have two tables in PowerQuery City and People.

城市

Id, Name
 1, Brisbane
 2, Sydney
 3, Melbourne

人

Id, Name,  CityId
 1, Jay,   1
 2, Sam,   2
 3, Paul,  1
 4, Sarah, 3

我想向 City 添加一列，显示属于该城市的人数.到目前为止，我有:

I'd like to add a column to City that shows a count of how many people belong to that city. So far I have:

Table.AddColumn(City, "People.Count", each
    Table.RowCount(
        Table.SelectRows(
            People, 
            each [CityId] = [Id]
        )
    )
)

这将在新列中返回全零.如果我用 1 替换 [Id] 那么我得到 2.

This returns all zeros in the new column. If I replace [Id] with 1 then I get 2.

似乎 [] 引用了当前行但在同一个表上的值.有没有办法可以写成

It appears the [] references the value on the current row but on the same table. Is there a way I can write it as

Table.AddColumn(City, "People.Count", each 
    Table.RowCount(
        Table.SelectRows(
            People, 
            each People[CityId] = City[Id]
        )
    )
)

在 Excel 中，这与

In Excel this would be as basic as

=COUNTIF(People[CityId],[@Id])

然后拉下 PEOPLE 中的新列(根据附件 .xlsx)

and then pulled down the new column in PEOPLE (as per attached .xlsx)

看起来很简单，但绝对卡住了！哎呀.

Seems simple enough but absolutely stuck! Eeek.

推荐答案

我会构建一个从 PEOPLE 开始的查询，然后按城市分组并计算一个计数.

I would build a Query that starts from PEOPLE, and then Groups By City and calculates a Count.

然后我会将 CITY 查询与该新查询合并并展开 Count 列.

Then I would Merge the CITY query with that new Query and expand the Count column.

不需要代码或公式 - 它实际上比 Excel 公式更简单.

No code or formulas are required - it's actually simpler than Excel formulas.

这篇关于Power Query - 在两个表中添加列 CountIF 即 =COUNTIF(People[CityId],[@Id])的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！