GroupBY Distinct count

[toobafarooqui08]

Hi

I have this table. I need to count the number of zeros and 1's A, B and D have. I am trying countx(filter) but can't decide where am I going wrong. 

ID	values
a	1
a	0
d	1
b	1
a	0
d	0
b	1
b	0

[BA_Pete · Accepted solution]

Hi @toobafarooqui08 ,

Multi-select (Ctrl+click) both columns. Go to the Home tab > Group By. Select Count Rows as your aggregator.

Example code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitWBsAzArBS4WBIOWQM02SSIWCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, values = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"ID", type text}, {"values", Int64.Type}}),
    groupBothColumns = Table.Group(chgTypes, {"ID", "values"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    groupBothColumns

Example output:

Pete