Power Query - Count and Sum excluding Specific Value

[Anonymous]

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, and Count3 but excluding the value 6

Example:

Row ABC:

1. 3 (all 3 Count columns have a value that is not 6)

2. Sum is 6 (2+2+2)

Row DEF:

1. 2 (Count2 and Count3 are included, but Count1 is excluded because it is a 6)

2. Sum is 1 (0+1)

If needed, I could replace 6 with null, but I was still having trouble figuring out ask #1

Any assistance would be appreciated.

Thank you,

[amitchandak · Accepted solution]

@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]

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.