Derive a Price Type based on Price history

[Anonymous]

Hey everybody,

Hope to find you well!
Trying to create a new column in the raw data that looks at the Gross Price of a product per specific store.
Anyone recommends a syntax that can do something like the column Type below?

Thanks and hope you are all well

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdAxDoAgDIXhuzAzUFowjnICE0fC/a8hxJjgz+Dyhi/0lbRWdx3Ou3NEUtf8ByLh94UYYR4pPWIiZMJGMMJSGgCyP1Def1gkKMEAGgAihLl0WTtCDRCVEAjC0kzoJ2w3", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store Type" = _t, Product = _t, #"Gross Price" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Store Type", "Product"}, {{"Temp", (x)=> Table.AddColumn(x,"Type",each if List.Contains(List.LastN(x[Gross Price],2),[Gross Price]) then "Promo" else "Full Price"), type table}}),
    #"Expanded Temp" = Table.ExpandTableColumn(#"Grouped Rows", "Temp", {"Gross Price", "Type"}, {"Gross Price", "Type"})
in
    #"Expanded Temp"