Forum Discussion
Solved
Count rows per group in power query
How can I do a count by group in the same table (Column = Count_Rows_by_Group)?
I managed to do it by creating another table grouped by category, but I have several other tables to do this same process and that way the panel will be very heavy.
You just need to group your data by Categoria then count your rows.
You can paste this on a new blank query source to try.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEyMzIGko65iSn5RQqOSrE60UqWQAFzQ0N0YUOQQjMDDOWGZmBxEwxxc5C4BcgSLwVnqCBYrbk5ihjIJjNjCxQxsF2WxihiIBvMzQ0RYrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ordem = _t, Column1 = _t, Column2 = _t]), #"Grouped Rows" = Table.Group(Source, {"Column2"}, {{"All", each _, type table [Ordem=nullable text, Column1=nullable text, Column2=nullable text]}, {"CountRowsbyGroup", each Table.RowCount(_), Int64.Type}}), #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Ordem", "Column1"}, {"Ordem", "Column1"}) in #"Expanded All"Hope this helps.
1 Reply
You just need to group your data by Categoria then count your rows.
You can paste this on a new blank query source to try.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEyMzIGko65iSn5RQqOSrE60UqWQAFzQ0N0YUOQQjMDDOWGZmBxEwxxc5C4BcgSLwVnqCBYrbk5ihjIJjNjCxQxsF2WxihiIBvMzQ0RYrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ordem = _t, Column1 = _t, Column2 = _t]), #"Grouped Rows" = Table.Group(Source, {"Column2"}, {{"All", each _, type table [Ordem=nullable text, Column1=nullable text, Column2=nullable text]}, {"CountRowsbyGroup", each Table.RowCount(_), Int64.Type}}), #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Ordem", "Column1"}, {"Ordem", "Column1"}) in #"Expanded All"Hope this helps.