cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper IV

## Conditional Column with sum function

Hi all,

Want to ask how can I do a formulation for STOP TIME column as per below excel formula. Already done successfully in DAX but I want to do it in the power query (M language) since I want to group it later.

Logic formulation as below (understand power query cannot do or function more than 2)

1. if value key "ZN04" or "ZN05" then sum(activity 1,activity 2,activity 3,activity 4,activity 5,activity 6) else 0

2. if value key "ZN19" or "ZN20" then sum(activity 1,activity 2,activity 3,activity 4,activity 5,activity 6) else 0

 Stop Time Value Key Activity 1 Activity 1 - Unit Activity 2 Activity 2 - Unit Activity 3 Activity 3 - Unit Activity 4 Activity 4- Unit Activity 5 Activity 5 - Unit Activity 6 Activity 6 - Unit ZN01 14.271 H 0.000 0.000 0.000 0.000 0.000 ZN02 85.627 H 14.271 H 14.271 H 14.271 H 14.271 H 0.000 0 ZN04 0 MIN 0 MIN 0 MIN 0 MIN 0 MIN 0 MIN 607 ZN05 431 MIN 0 MIN 0 MIN 176 MIN 0.000 0.000 ZN06 0.000 264.000 M 102.000 M 0.000 0.000 0.000 ZN07 0.000 0.000 0.000 0.000 0.000 0.000 ZN08 0.000 0.000 0.000 0.000 0.000 0.000 ZN09 0.000 0.000 0.000 0.000 0.000 0.000 ZN10 0.000 0.000 0.000 0.000 0.000 0.000 ZN11 0.000 0.000 0.000 0.000 0.000 0.000 ZN12 0.000 0.000 0.000 0.000 0.000 0.000 ZN13 0.000 0.000 463.000 M 0.000 0.000 0.000 ZN14 0.000 0.000 0.000 0.000 0.000 0.000 ZN15 0.000 0.000 0.000 0.000 0.000 0.000 ZN16 0.000 0.000 0.000 0.000 0.000 0.000 ZN17 0.000 0.000 0.000 0.000 0.000 0.000 ZN18 0.000 0.000 329.000 M 20.000 M 0.000 0.000
1 ACCEPTED SOLUTION
Super User

Use below formula in a custom column

``= if List.Contains({"ZN04","ZN05","ZN19","ZN20"},[Value Key]) then List.Sum({[Activity 1],[Activity 2],[Activity 3],[Activity 4],[Activity 5],[Activity 6]}) else 0``
2 REPLIES 2
Super User

Use below formula in a custom column

``= if List.Contains({"ZN04","ZN05","ZN19","ZN20"},[Value Key]) then List.Sum({[Activity 1],[Activity 2],[Activity 3],[Activity 4],[Activity 5],[Activity 6]}) else 0``
Helper IV

Thank you so much for your support. Case solved!

Regards,

Nuha