Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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"
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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"
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI think you are looking for sub total
https://powerbloggerbi.com/2016/06/29/power-query-total-and-subtotal/
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |