Forum Discussion
Solved
GroupBY Distinct count
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 |
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 groupBothColumnsExample output:
Pete
1 Reply
- BA_Pete
Super User
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 groupBothColumnsExample output:
Pete