cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nuhasan
Helper II
Helper II

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
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors