Column Value Based on Multiple Row Condition

[soji]

Hi, I am trying to build a report for items sold under a specific offer. But my data is not sufficient for the same as i have only five coulmns in the table (this is an example) and i would like to get the 6th coulmn (Desired Result) using Power Query / DAX. This offer is "Buy one Burger and Get one Burger Free with a Starter purchase" and below table is the example of data and the new column - Desired Result. Can someone help me to sortout this problem.

Order #	Item Code	Sold Qty	Category	Total	Desired Result
A01	10021	1	Burger	22	Offer
A01	10023	1	Burger	0	Offer
A01	10024	1	Starter	15	Offer
A01	10026	1	Drinks	10
A01	10030	1	Offer	0	Offer

Thanks

Soji Varghese

[BA_Pete · Accepted solution]

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

[soji]

Hi,

The offer is "Buy one Burger and Get one Burger Free if any starter purchase". My example table is about one transaction (A01) where five line items are there. Out of five , four lines are belongs to the offer  and one is not part (Drinks) of the offer - which i have mentioned in the desired result colum , i need your help to calculate the desired result value.

Here i know that item code 10030 is an offer - "Buy one Burger and Get one Burger Free if any starter purchase". So i will check for the transaction where 10030 item code is there in the line and will mark one starter  and two burger as offer  in the same transaction ( One burger with value and other with zero value as one is free as per the offer). Sameway if the Offer Quantity is two then will double the starter and burger quantity and so on.

[nandukrishnavs]

@soji  Your example is not clear. Can you provide few more rows with the Desired Result as blank.

[amitchandak]

@soji , we can relate burger, how to relate offer and starter or any other combination