Count occurrence

[bednarol]

I want to add a column and count how many times a certain Program Name occurs in column "Program" in table "Dimension". I tried to add custom column and following formula but get "COUNTX" not recognized.

=COUNTX(FILTER(EARLIER([Program])=[Program]),[Program])

Thanks!

[v-juanli-msft]

Hi @bednarol 

In Edit queries, you can group rows and add a count column.

Or, create a calculated column in Data model view,

Column = CALCULATE(COUNT('Table'[program]),ALLEXCEPT('Table','Table'[program]))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bednarol]

Grouping is obvious but I do not want to do that because I need to keep the table ungroup for futher data manipulation. Using the calculated column in Desktop in a good idea but I cannot figure out how to do that in Excel Power Query. Maybe that is one of the limitation?

[v-juanli-msft · Accepted solution]

Hi @bednarol 

In my first solution of the previous reply, i use "Group" function, but it would not change the data model, 

please note, i add two "operations",

one is "count"(count rows) based on "program" column, 

another is "all"(don't summarized), it means it would be the original columns.

then i get two columns, one is "count", another is "all", 

in "all" column, there are all my original columns nested here,

i click on "expand" icon from the "all" column and select columns i need, i can get all original columns.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nilimarodrigues]

This is an awesome idea! I learnt something new today. Thank you!

[VasTg]

@bednarol 

Try this..

Column = COUNTX(FILTER(Dimension,EARLIER(Dimension[Program])=Dimension[Program]),Dimension[Program])

If this helps, accept it as a solution.

Kudos are nice too.

[bednarol]

I am getting "COUNTX' not recognized. I should make clear that I am trying to use Power Query in Excel, not Power BI Desktop since the user who will be using this does not have desktop. I tried to enable addins that seem to be relevant. Is it something that is not available in Power Query?

[v-juanli-msft]

Hi @bednarol 

Your formula with "COUNTX' is a DAX formula, which is used in a measure or a calculated column in Power Pivot.

If you just use it in Power Query->Add a custom column, it should not work as default.

In Power Query, you can refer to my first solution in my previous reply.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.