Assign a number of occurrence to each record (sort of Count if)

[TamirBasin]

Hi,
How can I achieve the same calculation in Power Query?
In Excel I would use: =COUNTIF($A$2:A2,A2)

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

<tbody>
</tbody>

Regards,
Tamir

 

[Ron Coderre]

Here's what I did:
• I made your sample data a table named Table1
• Added that table to Power Query (as a query named SourceData)
• Created this query to assign the clustered indexes:

let
    Source = SourceData,
    InsertedIndex = Table.AddIndexColumn(Source,"Index"),
    AddGroupingTableToEachRows = Table.Group(InsertedIndex, {"Name"}, {{"Values", each _, type table}}),
    AddIndexToEachGroupedTable = Table.TransformColumns(AddGroupingTableToEachRows,{{"Values", each Table.AddIndexColumn(Table.Sort(_, "Index"),"GroupIndex",1)}}),
    ExposeCols = Table.ExpandTableColumn(AddIndexToEachGroupedTable, "Values", {"Index", "GroupIndex"}, {"Index", "GroupIndex"}),
    MergeIndexedDataWithGroupedIndex = Table.NestedJoin(InsertedIndex,{"Index"},ExposeCols,{"Index"},"NewColumn",JoinKind.LeftOuter),
    ExposeGroupIndexCol = Table.ExpandTableColumn(MergeIndexedDataWithGroupedIndex, "NewColumn", {"GroupIndex"}, {"GroupIndex"}),
    SortedIntoOrigOrder = Table.Sort(ExposeGroupIndexCol,{{"Index", Order.Ascending}})
in
    SortedIntoOrigOrder

• These are the results:

Name	Index	GroupIndex
A		1
A	1	2
B	2	1
A	3	3
B	4	2

<tbody>
</tbody>

Is that something you can work with?

 

[TamirBasin]

Thanks Ron,

The code seems to be exactly what I need.

I will try and work it out.

Could you provide some more explanations?

Regards,
Tamir

 

[Ron Coderre]

Sure.... Explanations of what part(s)?

 

[TamirBasin]

This one:

AddIndexToEachGroupedTable = Table.TransformColumns(AddGroupingTableToEachRows,{{"Values", each Table.AddIndexColumn(Table.Sort(_, "Index"),"GroupIndex",1)}}),

 

[Ron Coderre]

It might be easier if you look at what's happening via the edit window.
At the AddGroupingTableToEachRows step....click on one of the "Table" cells. You'll see that it contains all of the records for the specific Name...and it's position in the original listing.
Next, click on the AddIndexToEachGroupedTable step and click on the same "Table" cell. It now contains the Name, original position...and an incremental ordinal! That's the value we really want.

In the following steps we reference the InsertedIndex data and merged our new seq num table and sort the data into its original order.
Does that help?

 

[TamirBasin]

Thank you Ron,

I will grok it

Regards,
Tamir