Forum Discussion
Column Value Based on Multiple Row Condition
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
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 repNonOfferItemsGo 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
4 Replies
soji , we can relate burger, how to relate offer and starter or any other combination
soji Your example is not clear. Can you provide few more rows with the Desired Result as blank.
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.
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 repNonOfferItemsGo 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
