Counting occurences in Power Query

[Anonymous]

Hi all, 

I found my self in need of counting occurences of values in Power Query. I want to higlight that this needs to be done in Power Query, not as a DAX - formula (that I can do).

Here's an example:

Names	Calculated values
Anne	4
James	2
Anne	4
Anne	4
James	2
Frank	1
Anne	4

So each row should have the value of the total appearances of the value in one column. I imagine this is possible, and perhaps this an absolute novice question. But I just can't figure it out, so here I am. 

[MFelix · Accepted solution]

Hi @Anonymous ,

Do a Group By Name and select count Rows. Then just merge make the merge with the step before making the group by.

Check the code with the steps for a simple model. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcszLS1WK1YlW8krMTS0Gs+BCWOTcihLzspEkYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Names = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Names"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Merged Queries" = Table.NestedJoin(Source, {"Names"}, #"Grouped Rows", {"Names"}, "Count_Rows", JoinKind.LeftOuter),
    #"Expanded Count_Rows" = Table.ExpandTableColumn(#"Merged Queries", "Count_Rows", {"Count"}, {"Count"})
in
    #"Expanded Count_Rows"

[Anonymous]

Awesome, works like a charm! Thanks @MFelix , you totally saved my day! 

[amitchandak]

I think you are looking for sub total

https://powerbloggerbi.com/2016/06/29/power-query-total-and-subtotal/