Forum Discussion

bednarol's avatar
bednarol
New Member
6 years ago
Solved

Count occurrence

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 recogni...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

     

v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

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's avatar
bednarol
New Member
6 years ago

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's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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's avatar
      nilimarodrigues
      Helper III
      6 years ago

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