# Thread: Countif in power bi Thanks: 0 Likes: 0

1. ## Countif in power bi

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)  Reply With Quote

2. ## Re: Countif in power bi

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.  Reply With Quote

3. ## Re: Countif in power bi

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.

 ColA ColB AA1 1 AA2 1 AA1 2 AA4 1 AA5 1 AA2 2 AA7 1 AA8 1 AA1 3 AA4 2 Originally Posted by treaves04 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.  Reply With Quote

4. ## Re: Countif in power bi

Hi,

Have a look at Ken's article here:

https://www.excelguru.ca/blog/2018/0...g-power-query/

Cheers,

Matty  Reply With Quote

5. ## Re: Countif in power bi

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  Reply With Quote

6. ## Re: Countif in power bi

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.  Reply With Quote

7. ## Re: Countif in power bi

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

Code:
```let

Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],

Index_1 = Table.Buffer(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.Buffer(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

```  Reply With Quote

8. ## Re: Countif in power bi

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.  Reply With Quote

9. ## Re: Countif in power bi Originally Posted by zuriqi 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)

Does the sort order matter?

 ColA Counter AA1 1 AA1 2 AA1 3 AA2 1 AA2 2 AA4 1 AA4 2 AA5 1 AA7 1 AA8 1  Reply With Quote

10. ## Re: Countif in power bi

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

## User Tag List

=countif\$a\$1a2, column, count, countif, entire 