给每个记录分配一个出现次数（如果是，则为Count） [英] Assign a number of occurrence to each record (sort of Count if)

问题描述

如何在Power Query中实现相同的计算？

How can I achieve the same calculation in Power Query?

在Excel中，我将使用： = COUNTIF（$ A $ 2：A2， A2）

In Excel I would use: =COUNTIF($A$2:A2,A2)

Name    Occurrence
A          1
A          2
B          1
A          3
B          2

谢谢，
Tamir

Thanks, Tamir

推荐答案

只能通过按钮来实现（几乎-除某些自定义列公式外）：

This could be reached via "buttons" only (almost - except some custom column formula):

AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1),
GroupedRows = Table.Group(AddedIndex, {"Name"}, {{"tmp", each _, type table}}),
AddedCustom = Table.AddColumn(GroupedRows, "Custom", each Table.AddIndexColumn([tmp],"Occurrence", 1,1)),
RemovedOtherColumns = Table.SelectColumns(AddedCustom,{"Custom"}),
Expanded = Table.ExpandTableColumn(RemovedOtherColumns, "Custom", {"Name", "Occurrence"}, {"Name", "Occurrence"})

或者更短：

AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1),
GroupedRows = Table.Group(AddedIndex, {"Name"}, {{"tmp", each Table.AddIndexColumn(_, "Occurence", 1,1), type table}}),
Expanded = Table.ExpandTableColumn(GroupedRows, "tmp", {"Occurrence"}, {"Occurrence"})

如果需要保存，还可以提取 Index 列并对其进行排序初始行顺序。同样，所有其他必要的列也必须在最后一步中提取

There you can also extract Index column and sort by it, if you need to preserve initial row order. Also all other necessary columns have to be extracted on the last step

这篇关于给每个记录分配一个出现次数（如果是，则为Count）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！