Countif in power bi

zuriqi

Board Regular
Joined
Dec 8, 2008
Messages
79
Hi

I am looking for a similar to the below formula to find the count of item in each and every row in the entire column.

=COUNTIF($A$1:A2,A2)

=COUNTIF(Table1[[#Headers],[ColA]:[ColA]]:[@ColA],[@ColA])
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
zuriqi last replied on 30 May. But that's how I understood the task.


But I don't care, because when I help, it's primarily to learn for myself. And I also succeeded very well with this thread
 
Upvote 0
Try this:

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColA", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "ColA", "ColA - Copy"),
    #"Added Index" = Table.AddIndexColumn(#"Duplicated Column", "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"ColA - Copy"}, {{"CountAll", each _, type table [ColA=text, #"ColA - Copy"=text, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Index2", each Table.AddIndexColumn([CountAll], "Index2", 1, 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"CountAll"}),
    #"Expanded Index2" = Table.ExpandTableColumn(#"Removed Columns", "Index2", {"ColA", "Index", "Index2"}, {"ColA", "Index", "ColB"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Index2",{{"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"ColA", "Index"})
in
    #"Removed Columns1"

On my computer it lasts for:

Excelguru.ca solution - about 33 seconds
My solution - 5 seconds
 
Upvote 0
That's a great solution, too. At the test with 960.000 lines, it took about 13 seconds. Almost identical with the first solution from cyborgski, which takes about 12 seconds on my computer. Only the second solution of cyborgski needs about 40 seconds for this amount of data.


At the test with 120.000 lines all 3 solutions are, from the feeling, equally fast.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top