如何在 Power Query 中按组进行累加计数

v-stephen-msft · ‎12-05-2024

在本文中，我将介绍在 Power Query 中进行累加计数的两种场景。场景一是根据其他列进行分组和累加计数。场景二是在场景一的基础上进行的高级计算，不仅进行分组累加计数，还需要进行条件判断。虽然，这两种场景都是在 Power Query 中进行累加计数。但是场景一适用于整体情况，而场景二则适用于满足条件的特定部分。

示例数据

场景1：

在 Power Query 中根据 Id、SubId、Name 和Date顺序获取累加计数

预期结果：

在这种情况下，我们以Id = 1举例，

对于Id = 1, SubId = A, Name = AAA,有四行数据满足条件，所以按照时间按顺序返回的预期结果为1，2，3，4（蓝色框内）

对于Id = 1, SubId = A, Name = BBB,有三行数据满足条件，所以按照时间按顺序返回的预期结果为1，2，3（红色框内）

操作步骤：

打开高级编辑器，将红框内的代码复制并粘贴进去

以下是对于上述代码的解释：

步骤1：

首先，我们在 #“Sorted Rows”步骤的基础上按 Id、SubId、Name 和 Date 过滤表，然后得到满足条件的部分表。

Table.SelectRows( 
                        #"Sorted Rows", 
                        each [Id] = r[Id] and [SubId]=r[SubId] and [Name]=r[Name] and [Date] <= r[Date] 
                    )

步骤2：

然后从表中获取Name列

Table.SelectRows( 
                        #"Sorted Rows", 
                        each [Id] = r[Id] and [SubId]=r[SubId] and [Name]=r[Name] and [Date] <= r[Date] 
                    )[Name]

步骤3：

然后对于第二步中包含的数据进行计数

List.Count(                    
                    Table.SelectRows( 
                        #"Sorted Rows", 
                        each [Id] = r[Id] and [SubId]=r[SubId] and [Name]=r[Name] and [Date] <= r[Date] 
                    )[Name] 
                )

步骤4：

最后使用Table.AddColumn()函数向表中添加“Count”列

Table.AddColumn(#"Sorted Rows", "Count",  (r) => 
        List.Count(                    
                   Table.SelectRows( 
                        #"Sorted Rows", 
                        each [Id] = r[Id] and [SubId]=r[SubId] and [Name]=r[Name] and [Date] <= r[Date] 
                    )[Name] 
                ) 
            )

步骤5：

通过引用#"Add Column"步骤返回表

in 
    #"Add Column"

输出结果

场景2：

在Power Query中，首先确定Name是否为“AAA“，如果满足条件则根据Id，SubId和Date计算累加值,否则的话返回0。

预期结果：

在这种情况下，我们以Id = 1举例，

对于Id = 1, SubId = A, Name = AAA,有四行数据满足条件，所以按照时间按顺序返回的预期结果为1，2，3，4（红色框内）

操作步骤：

打开高级编辑器，将红框内的代码复制并粘贴进去

以下是对于上述代码的解释：

步骤1：

首先，我们在 #“Sorted Rows”步骤的基础上按 Id、SubId、Name 和 Date 过滤表，然后得到满足条件的部分表。

Table.SelectRows( 
                            #"Sorted Rows", 
                            each [Id] = r[Id] and [SubId]=r[SubId] and [Date] <= r[Date] 
                        )

步骤2：

然后从表中获取Name列

Table.SelectRows( 
                            #"Sorted Rows", 
                            each [Id] = r[Id] and [SubId]=r[SubId] and [Date] <= r[Date] 
                        )[Name]

步骤3：

然后过滤出Name列中值为”AAA”的行

List.LastN( 
                        Table.SelectRows( 
                            #"Sorted Rows", 
                            each [Id] = r[Id] and [SubId]=r[SubId] and [Date] <= r[Date] 
                        )[Name], 
                        each _ = "AAA" 
                    )

步骤4：

对于包含在步骤三中的数据进行计数

List.Count( 
                    List.LastN( 
                        Table.SelectRows( 
                            #"Sorted Rows", 
                            each [Id] = r[Id] and [SubId]=r[SubId] and [Date] <= r[Date] 
                        )[Name], 
                        each _ = "AAA" 
                    ) 
                )

步骤5：

最后，使用Table.AddColumn()函数向表中添加“Count”列。如果值为“AAA”,那么将会进行累加计算，否则返回0。

Table.AddColumn(#"Sorted Rows", "Count",  (r) => if r[Name] = "AAA" 
           then List.Count( 
                    List.LastN( 
                        Table.SelectRows( 
                            #"Sorted Rows", 
                            each [Id] = r[Id] and [SubId]=r[SubId] and [Date] <= r[Date] 
                        )[Name], 
                        each _ = "AAA" 
                    ) 
                ) 
           else 0)

步骤6：

通过引用#"Add Column"步骤返回表

in 
    #"Add Column"

输出结果：