Single Group By Step With Multiple Criteria In Power Query

[ajlindquist]

I have a table that I need to group by a single field, but I need to return multiple columns with different criteria. My dataset looks like this:

Job	Operation	Description	Complete
ABC	10	First Operation	TRUE
ABC	20	Second Operation	TRUE
ABC	30	Third Operation	FALSE
ABC	40	Fourth Operation	FALSE
XYZ	10	First Operation	FALSE
XYZ	20	Second Operation	FALSE
XYZ	30	Third Operation	FALSE
XYZ	40	Fourth Operation	FALSE

I need to group by the Job column, and return 6 columns with the following criteria:

• First Op = operation with the lowest value
• First Op Desc = description associated with the operation with the lowest value
• Last Compl Op = operation with the highest value where "Complete" = TRUE
• Last Compl Op Desc = the description associated with the operation with the highest value where "Complete" = TRUE
• Current Op = operation with the lowest value where "Complete" = FALSE
• Current Op Desc = the description associated with the operation with the lowest value where "Complete" = FALSE

This is the desired output:

Job	First Op	First Op Desc	Last Compl Op	Last Compl Op Desc	Current Op	Current Op Desc
ABC	10	First Operation	20	Second Operation	30	Third Operation
XYZ	10	First Operation	null	null	10	First Operation

In this example, job XYZ has no completed operations so the Last Compl Op and Last Compl Op Desc should return null.

Is it possible to do this in a single group by step?

[dufoq3 · Accepted solution]

Hi @ajlindquist,

result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMjQAEm6ZRcUlCv4FqUWJJZn5eUCRkKBQV6VYHZgqI5Cq4NTk/LwUfMqMQcpCMjKLUFW5OfoEIyszAduZX1pUkoFDXURkFG63oSvD5Th0dQRdB1FGwHWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Job = _t, Operation = _t, Description = _t, Complete = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Operation", type number}, {"Complete", type logical}}),
    GroupedRows = Table.Group(ChangedType, {"Job"}, {
      { "First Op", each Table.First(Table.Sort(_, {"Operation", Order.Ascending}))[Operation], type nullable number },
      { "First Op Desc", each Table.First(Table.Sort(_, {"Operation", Order.Ascending}))[Description], type text },
      { "Last Compl Op", each Record.FieldOrDefault(Table.Last(Table.SelectRows(Table.Sort(_, {"Operation", Order.Ascending}), (x)=> x[Complete] = true )), "Operation", null), type nullable number },
      { "Last Compl Op Desc", each Record.FieldOrDefault(Table.Last(Table.SelectRows(Table.Sort(_, {"Operation", Order.Ascending}), (x)=> x[Complete] = false )), "Description", null), type text },
      { "Current Op", each Record.FieldOrDefault(Table.First(Table.SelectRows(Table.Sort(_, {"Operation", Order.Ascending}), (x)=> x[Complete] = false )), "Operation", null), type nullable number },
      { "Current Op Desc", each Record.FieldOrDefault(Table.First(Table.SelectRows(Table.Sort(_, {"Operation", Order.Ascending}), (x)=> x[Complete] = false )), "Description", null), type text }
})
in
    GroupedRows