Custom Column based on comparing a value against a shared identifier

Hi BenDC ,

In Power Query, you can group then expand the same table.

1) Group by [Order ID] and create an 'All Rows' aggregate column

2) Add a custom column that evaluates the [Training] column of the nested tables

3) Expand your grouped rows ack out again

Paste this over the default code in Advanced Editor to follow the steps I took:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeOQoFBXIOXm6BPsqhSrA5EwwiVhDBeBKoiNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OrderID = _t, OrderLineID = _t, Hardware = _t, Training = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"OrderID", Int64.Type}, {"OrderLineID", Int64.Type}, {"Hardware", type logical}, {"Training", type logical}}),
    groupRows = Table.Group(chgTypes, {"OrderID"}, {{"data", each _, type table [OrderID=nullable number, OrderLineID=nullable number, Hardware=nullable logical, Training=nullable logical]}}),
    addTrainingOrder = Table.AddColumn(groupRows, "trainingOrder", each if List.Contains([data][Training], true) then "TRUE" else "FALSE"),
    expandDataCol = Table.ExpandTableColumn(addTrainingOrder, "data", {"OrderLineID", "Hardware", "Training"}, {"OrderLineID", "Hardware", "Training"})
in
    expandDataCol

Pete