Column Value Based on Multiple Row Condition

Hi soji ,

You can complete this in Power Query as follows:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwVNJRMjQwMALTQOxUWpSeWgRkGBkpxeogqzBGV2GApsAEqiC4JLGoBKzC0BRNiRlUiUtRZl52MVgQVYWxAVSFf1oazJJYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order #" = _t, #"Item Code" = _t, #"Sold Qty" = _t, Category = _t, Total = _t]),
    chgAllTypes = Table.TransformColumnTypes(Source,{{"Order #", type text}, {"Item Code", type text}, {"Sold Qty", Int64.Type}, {"Category", type text}, {"Total", Int64.Type}}),
    addCatTotal = Table.AddColumn(chgAllTypes, "catTotal", each Text.Combine({[Category], Text.From([Total], "en-GB")}, ""), type text),
    #"groupOrder#" = Table.Group(addCatTotal, {"Order #"}, {{"data", each _, type table [#"Order #"=text, Item Code=text, Sold Qty=number, Category=text, Total=number, catTotal=text]}}),
    addDesiredResult = Table.AddColumn(#"groupOrder#", "desiredResult", each if List.Contains([data][Category], "Offer") and List.Contains([data][Category], "Starter") and List.Contains([data][catTotal], "Burger0") then "Offer" else null),
    expandData = Table.ExpandTableColumn(addDesiredResult, "data", {"Item Code", "Sold Qty", "Category", "Total"}, {"Item Code", "Sold Qty", "Category", "Total"}),
    repNonOfferItems = Table.ReplaceValue(expandData, each [desiredResult], each if [desiredResult] = "Offer" and [Category] <> "Burger" and [Category] <> "Starter" and [Category] <> "Offer"  then null else [desiredResult] ,Replacer.ReplaceValue,{"desiredResult"})
in
    repNonOfferItems

Go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this.

Summary:

1) Combine [Category] and [Total] to identify where there is a Burger with zero value in the order.

2) Group by Order# to appraise each order individually.

3) If there's an Offer item, a Starter item, and a Burger item with zero value in the order, then apply desired result "Offer" to order.

4) Remove the "Offer" flag from [desiredResult] where the item is not a Burger, Starter, or Offer item.

I get the following output:

Pete