# Countif in power bi



## zuriqi (May 28, 2019)

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])


----------



## treaves04 (May 28, 2019)

Are you trying to count the number of items, or get a total of the values in the cells?  =SUMIFS will do a better job of totaling the value of the cells.


----------



## zuriqi (May 30, 2019)

No actually i want to create a new column. in that column, i want to count the repeated cell values in the previous column by locking the first cell while counting as shown below. I can do this in excel tables but i want to do it in power bi. 


ColAColBAA11AA21AA12AA41AA51AA22AA71AA81AA13AA42

 <colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>

</tbody>



treaves04 said:


> Are you trying to count the number of items, or get a total of the values in the cells?  =SUMIFS will do a better job of totaling the value of the cells.


----------



## Matty (Jun 1, 2019)

Hi,

Have a look at Ken's article here:

https://www.excelguru.ca/blog/2018/06/27/number-rows-by-group-using-power-query/

Cheers,

Matty


----------



## pinarello (Jun 1, 2019)

It was a little bit tricky but following query works as expected:

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    Index_1 = Table.AddIndexColumn(Source, "Index", 0, 1),
    Sorted_Lines = Table.Sort(Index_1,{{"ColA", Order.Ascending}, {"Index", Order.Ascending}}),
    Index_2 = Table.AddIndexColumn(Sorted_Lines, "Index-2", 1, 1),
    Index_3 = Table.AddIndexColumn(Index_2, "Index-3", 0, 1),
    Userdefinded_Col = Table.AddColumn(Index_3, "Count_from", each if[#"Index-3"] = 0 or [ColA] <> Index_3[ColA]{[#"Index-3"]-1} then [#"Index-2"] else null),
    Fill_down = Table.FillDown(Userdefinded_Col,{"Count_from"}),
    Column_B = Table.AddColumn(Fill_down, "Col-B", each [#"Index-3"] - [Count_from] + 2),
    Sort_by_original_order = Table.Sort(Column_B,{{"Index", Order.Ascending}}),
    Remove_all_help_columns = Table.RemoveColumns(Sort_by_original_order,{"Index", "Index-2", "Index-3", "Count_from"})


in
    Remove_all_help_columns


----------



## pinarello (Jun 1, 2019)

I tested the query with 11000 lines and had to wait almost 7 minutes to get the result.


So you can probably see that queries that use an index to access other lines are not one of Power Query's strengths at the moment.


----------



## pinarello (Jun 6, 2019)

If I use Table.Buffer, the runtime of 11,000 records is reduced to about 50 seconds. This is much better than 7 minutes, but still not good enough


```
[TABLE="width: 988"]
<colgroup><col></colgroup><tbody>[TR]
[TD]let[/TD]
[/TR]
[TR]
[TD]    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],[/TD]
[/TR]
[TR]
[TD]    Index_1 = Table.Buffer(Table.AddIndexColumn(Source, "Index", 0, 1)),[/TD]
[/TR]
[TR]
[TD]    Sorted_Lines = Table.Sort(Index_1,{{"ColA", Order.Ascending}, {"Index", Order.Ascending}}),[/TD]
[/TR]
[TR]
[TD]    Index_2 = Table.AddIndexColumn(Sorted_Lines, "Index-2", 1, 1),[/TD]
[/TR]
[TR]
[TD]    Index_3 = Table.Buffer(Table.AddIndexColumn(Index_2, "Index-3", 0, 1)),[/TD]
[/TR]
[TR]
[TD]    Userdefinded_Col = Table.AddColumn(Index_3, "Count_from", each if[#"Index-3"] = 0 or [ColA] <> Index_3[ColA]{[#"Index-3"]-1} then [#"Index-2"] else null),[/TD]
[/TR]
[TR]
[TD]    Fill_down = Table.FillDown(Userdefinded_Col,{"Count_from"}),[/TD]
[/TR]
[TR]
[TD]    Column_B = Table.AddColumn(Fill_down, "Col-B", each [#"Index-3"] - [Count_from] + 2),[/TD]
[/TR]
[TR]
[TD]    Sort_by_original_order = Table.Sort(Column_B,{{"Index", Order.Ascending}}),[/TD]
[/TR]
[TR]
[TD]    Remove_all_help_columns = Table.RemoveColumns(Sort_by_original_order,{"Index", "Index-2", "Index-3", "Count_from"})[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]in[/TD]
[/TR]
[TR]
[TD]    Remove_all_help_columns[/TD]
[/TR]
</tbody>[/TABLE]
```


----------



## pinarello (Jun 23, 2019)

Hello,


since my solution produces correct results, but I was not satisfied with the runtime, I asked on excelguru for a better solution and got there two solution variants, which are many faster and have extended my PQ horizon enormously.


Here is the link


----------



## sandy666 (Jun 23, 2019)

zuriqi said:


> 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.
> 
> ...



Does the sort order matter?


*ColA**Counter*AA11​AA12​AA13​AA21​AA22​AA41​AA42​AA51​AA71​AA81​


----------



## pinarello (Jun 23, 2019)

Yes, the original order should be retained at the end. Both my and the linked solutions take this into account


----------



## zuriqi (May 28, 2019)

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])


----------



## sandy666 (Jun 23, 2019)

pinarello said:


> Yes, the original order should be retained at the end



Are you zuriqi?


----------



## pinarello (Jun 23, 2019)

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


----------



## sandy666 (Jun 23, 2019)

pinarello said:


> But that's how *I understood* the task.


But not me, in PBI it doesn't matter too much sort order


----------



## citizenbh (Jun 24, 2019)

Try this:


```
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


----------



## pinarello (Jun 24, 2019)

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.


----------

