Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

power query column sum in if statement

I am trying to use the following code for a custom colum to find the summation of values in the "Custom" column that also have "B" and "Knowledge in the row. Why is this not working?

 

if [Column] = "B" and [Bucket] = "Knowledge" then List.Sum([Custom]) else "null"

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    I think you need to add a list into List.Sum() function. Try this code.

    if 
[Column] = "B" and [Bucket] = "Knowledge" 
then 
List.Sum(
Table.SelectRows(#"Changed Type", each ([Column] = "B" and [Bucket] = "Knowledge"))[Custom]
)
else
null

    Result is as below.

    If you only want to get sum total based on [Column] = "B" and [Bucket] = "Knowledge" in the whole new column.

    I think you don't need IF() function.

    List.Sum(
Table.SelectRows(#"Changed Type", each ([Column] = "B" and [Bucket] = "Knowledge"))[Custom]
)

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • Hariharan_R's avatar
    Hariharan_R
    Solution Sage
    4 years ago

    Hi

    Your column [Custom] is a list? You can use List.sum if you are referring a list.

    Thanks

    Hari

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    I think you need to add a list into List.Sum() function. Try this code.

    if 
[Column] = "B" and [Bucket] = "Knowledge" 
then 
List.Sum(
Table.SelectRows(#"Changed Type", each ([Column] = "B" and [Bucket] = "Knowledge"))[Custom]
)
else
null

    Result is as below.

    If you only want to get sum total based on [Column] = "B" and [Bucket] = "Knowledge" in the whole new column.

    I think you don't need IF() function.

    List.Sum(
Table.SelectRows(#"Changed Type", each ([Column] = "B" and [Bucket] = "Knowledge"))[Custom]
)

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.