Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power Query - Count and Sum excluding Specific Value

Hello,   I am trying to do two things within power query: 1. For each row, determine how many from Count1, Count2, and Count3 has a value excluding the value 6 2. For each row, sum Count1, Count2...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , Assume these are columns and you need a new column in the power query

     

    Count =

    List.Count(List.Select({[Count1],[Count2],[Count3]}, each _ <> 6 ))

     

     

    Sum =

    List.Sum(List.Select({[Count1],[Count2],[Count3]}, each _ <> 6 ))

     

     

    Power Query- List.Select: https://youtu.be/WYi_3IlhccM

     

    Power Query- List.Sum. Sum of a column in Power Query: https://youtu.be/Mud1RSqW5pw

     

    Power Query - List.Count and List.Distinct: https://youtu.be/zNREVnoAHwM

amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous , Assume these are columns and you need a new column in the power query

 

Count =

List.Count(List.Select({[Count1],[Count2],[Count3]}, each _ <> 6 ))

 

 

Sum =

List.Sum(List.Select({[Count1],[Count2],[Count3]}, each _ <> 6 ))

 

 

Power Query- List.Select: https://youtu.be/WYi_3IlhccM

 

Power Query- List.Sum. Sum of a column in Power Query: https://youtu.be/Mud1RSqW5pw

 

Power Query - List.Count and List.Distinct: https://youtu.be/zNREVnoAHwM

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thank you for the solution and providing some links for me to get more information.

 

Still trying to get use to all the possible functions.