Count of subcategories

[jerryr0125]

Hi everyone  Using Power Query -

I am looking to do a count of subcategories by creating a new custom column.

Example:

Field-MainCategory	Field-subcategory
A	55
A	33
B	33
B	44
C	77
D	55
D	44
D	77

New

Field-MainCategory	Field-subcategory	Field-scount
A	55	2
A	33	2
B	33	2
B	44	2
C	77	1
D	55	3
D	44	3
D	77	3

Basically - I am looking to create a new field Field-scount which is a count of occurances of the Field-MainCategory,

There are other important columns in the table so a group by will not do the trick.

Any ideas ? Thanks - Jerry

[AlienSx · Accepted solution]

group by category, keep all rows as one column, count rows in 2nd and expand table column after. 

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], 
    group = Table.Group(Source, "Field-MainCategory", {{"all", each _}, {"Field-scount", Table.RowCount}}),
    expand = Table.ExpandTableColumn(group, "all", {"Field-subcategory"})
in
    expand