Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Counting occurences in Power Query

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:

NamesCalculated values
Anne4
James2
Anne 4
Anne4
James2
Frank1
Anne4

 

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. 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
MFelix
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Awesome, works like a charm! Thanks @MFelix , you totally saved my day! 

amitchandak
Super User
Super User

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.