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

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

treaves04

Board Regular
Joined
Jul 2, 2012
Messages
55
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
Joined
Dec 8, 2008
Messages
79
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.

ColAColB
AA11
AA21
AA12
AA41
AA51
AA22
AA71
AA81
AA13
AA42

<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
Joined
Jun 1, 2019
Messages
33
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
Joined
Jun 1, 2019
Messages
33
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
Joined
Jun 1, 2019
Messages
33
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
Joined
Jun 1, 2019
Messages
33
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,438
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])
Does the sort order matter?

ColACounter
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
Joined
Jun 1, 2019
Messages
33
Yes, the original order should be retained at the end. Both my and the linked solutions take this into account
 

Watch MrExcel Video

Forum statistics

Threads
1,099,754
Messages
5,470,581
Members
406,707
Latest member
drkjz

This Week's Hot Topics

Top