Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
Proud to be a Datanaut!
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
Proud to be a Datanaut!