Conditional Column with sum function

[nuhasan]

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

[Vijay_A_Verma · Accepted solution]

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

[nuhasan]

hI @Vijay_A_Verma ,

Thank you so much for your support. Case solved!

Regards,

Nuha