如何在Power Query中实现类似Excel COUNTIF($A$2:A2,A2)的行内计数？

Got it, let's figure out how to replicate that Excel COUNTIF($A$2:A2,A2) running count behavior in Power Query. I’ve got two solid methods for you, depending on your dataset size and preference:

This method is way more efficient for big datasets, since it leverages grouping instead of slow row-by-row calculations:

• Start with your source table (let’s assume it’s named Source). Select the Name column, then go to the Transform tab and click Group By.
• In the Group By dialog box, configure these settings:
  • Group by: Name
  • New column name: Rows (or any name you like)
  • Operation: All Rows
• Click OK, and you’ll have a table where each row contains a Name and a nested table of all its occurrences.
• Next, add a custom column with this formula:

  Table.AddIndexColumn([Rows], "Occurrence", 1, 1)
  

  This adds an index starting at 1 to each nested group of rows.
• Finally, click the expand icon on the custom column you just created, choose to expand all columns, and clean up by removing the original Rows column. Rearrange columns if needed, and you’ll get your desired running count!

If you want a direct 1:1 match to how Excel’s COUNTIF works (great for small datasets), use this approach:

• Add a custom column to your source table with this formula:

  List.Count(List.Select(List.Range(Source[Name], 0, Table.PositionOf(Source, [Name]) + 1), (x) => x = [Name]))
  

  Let’s break this down:
  • Table.PositionOf(Source, [Name]) gets the current row’s position in the table
  • List.Range(Source[Name], 0, ...) grabs all Name values from the start of the column up to and including the current row
  • List.Select filters that list to only match the current row’s Name
  • List.Count counts those matches, just like Excel’s COUNTIF($A$2:A2,A2)

Either method will give you the exact output you showed:

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

内容的提问来源于stack exchange，提问作者Tamir