sum rows based on condition

[BI_Cub]

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:

Query: 

Year	Department	Classification	Qty
2018	Dept 1	Assistant	1
2018	Dept 1	Instructor	2
2018	Dept 1	Other	5
2018	Dept 2	Assistant	5
2019	Dept 1	Instructor	1
2019	Dept 1	Other	2
2019	Dept 1	Assistant	4
2019	Dept 2	Assistant	3

This is what I want:

Year	Department	Classification	Qty
2018	Dept 1	Assistant	1
2018	Dept 1	Instructor	2
2018	Dept 1	Other	5
2018	Dept 1	Instructor/Other	7
2019	Dept 1	Assistant	4
2019	Dept 1	Instructor	1
2019	Dept 1	Other	2
2019	Dept 1	Instructor/Other	3
2019	Dept 2	Assistant	3

Thanks for the help! 

[wdx223_Daniel]

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])

[jennratten]

Here is how you can do it.  

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtFDSUXJJLShRMAQyHIuLM4tLEvNKgGxDpVgdTBWeecUlRaXJJflFQI4RViX+JRmpIFlTDFkjNCvgKixxWmGIVQnMCiOssshWmGCoQHeEsVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Department = _t, Classification = _t, #"Qty " = _t]),
    ChangeTypes = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Department", type text}, {"Classification", type text}, {"Qty ", Int64.Type}}),
    Table1 = Table.AddColumn(ChangeTypes, "Level", each 1, Int64.Type),
    RenameColumns = Table.RenameColumns(Table1,{{"Classification", "ClassificationOld"}}),
    Classification = Table.AddColumn(RenameColumns, "Classification", each if [ClassificationOld] = "Instructor" or [ClassificationOld] = "Other" then "Instructor/Other" else "", type text),
    SelectRows = Table.SelectRows(Classification, each ([Classification] = "Instructor/Other")),
    Group = Table.Group(SelectRows, {"Year", "Department", "Classification"}, {{"Qty ", each List.Sum([#"Qty "]), type nullable number}}),
    Table2 = Table.AddColumn(Group, "Level", each 2, Int64.Type),
    Append = Table.Combine ( { Table1, Table2 } ),
    #"Sorted Rows" = Table.Sort(Append,{{"Year", Order.Ascending}, {"Department", Order.Ascending}, {"Level", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Level"})
in
    #"Removed Columns"

[BI_Cub]

Thank you for your responses these have been helpful and got it done!