Forum Discussion

hashtag_pete's avatar
hashtag_pete
Icon for Helper V rankHelper V
4 years ago
Solved

count occurences of multiple values

Hello community, 

I struggle with the following request:

I count the occurence of a certain value (here 50) in multiple columns with this query

let
    source = 
    #table(
        {"Width A", "Width B", "Width C"},
        {{50, 50, 50}, {40, 50, 50}, {40, 40, 40}, {45, 40, 40}}
    ),
    #"Changed Type" = Table.TransformColumnTypes(source,{{"Width A", Int64.Type}, {"Width B", Int64.Type}, {"Width C", Int64.Type}}),
    #"Count 50&45" = Table.AddColumn(#"Changed Type", "Count", each 
        List.Count(
            List.Select(
                Record.ToList(
                    Record.SelectFields(_, {"Width A", "Width B", "Width C"})
                ), each _ = 50 
            )
        )
    )
in
    #"Count 50&45"

 this shall now also count the number 45, so that the fourth row in the example returns 1

How can I achieve this? It sounds so simple, but I can't figure it out... 

 

thanks a lot in advance

4 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional

    Change each _ = 50 to

    each _ = 50 or _ = 45

  • OMG... unbelievable... I knew the solution would be simple, but these syntax errors always give me rest...

    Thanks so much.

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User

      Tip: If you need to check for more than just a couple of values, you can use List.Contains instead of a bunch of comparisons combined with "or".

       

      For example, instead of

      each _ = 50 or _ = 49 or _ = 48 or _ = 47 or _ = 46 or _ = 45

      write the following:

      each List.Contains({50,49,48,47,46,45}, _)
      • hashtag_pete's avatar
        hashtag_pete
        Icon for Helper V rankHelper V

        Thanks, AlexisOlson , I was looking for something similar to the IN statement in DAX, but wasn't succesfull. 

        This defenitely helps!