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

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
Joined
Jul 2, 2012
Messages
60
Office Version
365
Platform
Windows
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

ADVERTISEMENT

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

ADVERTISEMENT

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
6,355
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

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