Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
nuhasan
Helper IV
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 TimeValue KeyActivity 1Activity 1 - UnitActivity 2Activity 2 - UnitActivity 3Activity 3 - UnitActivity 4Activity 4- UnitActivity 5Activity 5 - UnitActivity 6Activity 6 - Unit
 ZN0114.271H0.000 0.000 0.000 0.000 0.000 
 ZN0285.627H14.271H14.271H14.271H14.271H0.000 
0ZN040MIN0MIN0MIN0MIN0MIN0MIN
607ZN05431MIN0MIN0MIN176MIN0.000 0.000 
 ZN060.000 264.000M102.000M0.000 0.000 0.000 
 ZN070.000 0.000 0.000 0.000 0.000 0.000 
 ZN080.000 0.000 0.000 0.000 0.000 0.000 
 ZN090.000 0.000 0.000 0.000 0.000 0.000 
 ZN100.000 0.000 0.000 0.000 0.000 0.000 
 ZN110.000 0.000 0.000 0.000 0.000 0.000 
 ZN120.000 0.000 0.000 0.000 0.000 0.000 
 ZN130.000 0.000 463.000M0.000 0.000 0.000 
 ZN140.000 0.000 0.000 0.000 0.000 0.000 
 ZN150.000 0.000 0.000 0.000 0.000 0.000 
 ZN160.000 0.000 0.000 0.000 0.000 0.000 
 ZN170.000 0.000 0.000 0.000 0.000 0.000 
 ZN180.000 0.000 329.000M20.000M0.000 0.000 
1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
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

View solution in original post

2 REPLIES 2
Vijay_A_Verma
Super User
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

hI @Vijay_A_Verma ,

 

Thank you so much for your support. Case solved!

 

Regards,

Nuha

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors