Change the first value and the others differently by condition

[Divous]

Hi community,

My table

buyer	seller	product	pay_opt	price
john	anna	apple	invoice	100
jack	anna	apple	invoice	200
john	robert	cherry	invoice	100
john	anna	apple	cash	300
jack	robert	cherry	invoice	200
frank	anna	apple	cash	100
john	anna	cherry	cash	100
jack	anna	apple	invoice	200

I need change values by this rules:

1) For every seller who have Apple and also have buyer John then change price to 200 - but only on the first occurrence of this combination

2) For every seller who have Cherry and also have buyer John then change price to 500 - but only on the first occurrence of this combination

3) For both of these first (1,2) combination also change Pay_opt to cash - only in situation in 1) and 2) and rest rows with this combination change Pay_opt to invoice

Desired output:

buyer	seller	product	pay_opt	price
john	anna	apple	cash	200
jack	anna	apple	invoice	200
john	robert	cherry	cash	500
john	anna	apple	invoice	300
jack	robert	cherry	invoice	200
frank	anna	apple	cash	100
john	anna	cherry	cash	500
jack	anna	apple	invoice	200

Is this possible in PQ?

Thanks in advance

Divous

[mahoneypat · Accepted solution]

Here's one way to do it. Just create a blank query, open the Advanced Editor, and replace the code there with the below, to see how it works. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WysrPyFPSUUrMy0sEUQUFOalAOjOvLD8zGcQyNDBQitUBqktMzsanzgimDmJeUX5SalEJkJGckVpUVIndSKxWJycWZwApY1R78ZkHszqtKDEPixuhBmK3FW4cuipivBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [buyer = _t, seller = _t, product = _t, pay_opt = _t, price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"buyer", type text}, {"seller", type text}, {"product", type text}, {"pay_opt", type text}, {"price", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"buyer", "product", "seller"}, {{"AllRows", each _, type table [#"buyer"=nullable text, #"seller"=nullable text, #"product"=nullable text, #"pay_opt"=nullable text, price=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddRankColumn([AllRows], "BuyerProductRank", {"Index", Order.Ascending})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", { "pay_opt", "price", "BuyerProductRank"}, {  "pay_opt", "price", "BuyerProductRank"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"seller", type text},  {"pay_opt", type text}, {"price", Int64.Type}, {"BuyerProductRank", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "NewFields", each if [product]="apple" and [buyer] = "john" then [NewPrice = if [BuyerProductRank] = 1 then 200 else [price], NewPay_Opt = if [BuyerProductRank] = 1 then "cash" else "invoice"] else if [product]= "cherry" and [buyer] = "john" then [NewPrice = if [BuyerProductRank] = 1 then 500 else [price], NewPay_Opt = if [BuyerProductRank] = 1 then "cash" else "invoice"] else [NewPrice = [price], NewPay_Opt = [pay_opt]]),
    #"Expanded NewFields" = Table.ExpandRecordColumn(#"Added Custom1", "NewFields", {"NewPrice", "NewPay_Opt"}, {"NewPrice", "NewPay_Opt"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded NewFields",{"pay_opt", "price"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"NewPrice", Int64.Type}, {"NewPay_Opt", type text}})
in
    #"Changed Type2"

Pat