Countif in power bi

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

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

 

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

 

[zuriqi]

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.

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]ColA[/TD]
[TD="class: xl64, width: 64"]ColB[/TD]
[/TR]
[TR]
[TD="class: xl65"]AA1[/TD]
[TD="class: xl66"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]AA2[/TD]
[TD="class: xl66"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]AA1[/TD]
[TD="class: xl66"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]AA4[/TD]
[TD="class: xl66"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]AA5[/TD]
[TD="class: xl66"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]AA2[/TD]
[TD="class: xl66"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]AA7[/TD]
[TD="class: xl66"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]AA8[/TD]
[TD="class: xl66"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]AA1[/TD]
[TD="class: xl66"]3[/TD]
[/TR]
[TR]
[TD="class: xl67"]AA4[/TD]
[TD="class: xl68"]2[/TD]
[/TR]
</tbody>[/TABLE]

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]

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]

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]

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]

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]

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]

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?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]ColA[/td][td=bgcolor:#70AD47]Counter[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]AA1[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]AA1[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]AA1[/td][td=bgcolor:#E2EFDA]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]AA2[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]AA2[/td][td=bgcolor:#E2EFDA]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]AA4[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]AA4[/td][td=bgcolor:#E2EFDA]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]AA5[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]AA7[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]AA8[/td][td]

1​

[/td][/tr]
[/table]

 

[pinarello]

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