Conditional Column with sum function

Question

Hi all,&nbsp;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.&nbsp;Logic formulation as below (understand power query cannot do or function more than 2)&nbsp;1. if value key "ZN04" or "ZN05" then sum(activity 1,activity 2,activity 3,activity 4,activity 5,activity 6) else 02. if value key&nbsp;"ZN19" or "ZN20" then sum(activity 1,activity 2,activity 3,activity 4,activity 5,activity 6) else 0&nbsp;&nbsp;Stop TimeValue KeyActivity 1Activity 1 - UnitActivity 2Activity 2 - UnitActivity 3Activity 3 - UnitActivity 4Activity 4- UnitActivity 5Activity 5 - UnitActivity 6Activity 6 - Unit&nbsp;ZN0114.271H0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;&nbsp;ZN0285.627H14.271H14.271H14.271H14.271H0.000&nbsp;0ZN040MIN0MIN0MIN0MIN0MIN0MIN607ZN05431MIN0MIN0MIN176MIN0.000&nbsp;0.000&nbsp;&nbsp;ZN060.000&nbsp;264.000M102.000M0.000&nbsp;0.000&nbsp;0.000&nbsp;&nbsp;ZN070.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;&nbsp;ZN080.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;&nbsp;ZN090.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;&nbsp;ZN100.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;&nbsp;ZN110.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;&nbsp;ZN120.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;&nbsp;ZN130.000&nbsp;0.000&nbsp;463.000M0.000&nbsp;0.000&nbsp;0.000&nbsp;&nbsp;ZN140.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;&nbsp;ZN150.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;&nbsp;ZN160.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;&nbsp;ZN170.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;0.000&nbsp;&nbsp;ZN180.000&nbsp;0.000&nbsp;329.000M20.000M0.000&nbsp;0.000&nbsp;

vijay_a_verma · Accepted Answer

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

Forum Discussion

Conditional Column with sum function

2 Replies

Related content

Conditional Column with Multiple Conditions

IF Function - 2 Conditions,

Conditional column

Conditional column-IFAND function

IF function with multiple conditions

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