# Countif in power bi

#### zuriqi

##### Board Regular
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)

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### treaves04

##### Board Regular
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

##### Board Regular
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

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

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.

#### pinarello

##### New Member

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

##### New Member
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

##### New Member

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

##### New Member
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.

#### sandy666

##### Well-known Member
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​

Last edited:

#### pinarello

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

1,106,262
Messages
5,510,167
Members
408,779
Latest member
Lermiapolar

### This Week's Hot Topics

• Turn fraction around
Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
• TIme Clock record reformatting to ???
Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
• TextBox Match
hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
• Using Large function based on Multiple Criteria
Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
• Can you check my code please
Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
• Combining 2 pivot tables into 1 chart
Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...