Forum Discussion
Derive a Price Type based on Price history
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
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"
1 Reply
- Vijay_A_Verma
Most Valuable Professional
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"