Forum Discussion
sum rows based on condition
Hello, I want to add two rows based on a value in one of the columns in my query. Add up the Qty for instructor and other for each year and department and have it as a new row. Here is a sample: ...
NewStep= let a={"Instructor","Other"} in Table.Combine(Table.Group(PreviousStepName,{"Year","Department"},{"n",each let s=List.Sum(List.Transform(Table.ToRows(_),each if List.Contains(a,_{2}) then _{3} else 0)) in if s=0 then _ else _&#table(Table.ColumnNames(_),{{[Year]{0},[Department]{0},Text.Combine(a,"/"),s}})})[n])