Forum Discussion
Anonymous
3 years agoNot applicable
Calculate row difference based on conditions
Looking for some help to implement this in power query.
Thanks!
1 Reply
- m_dekorteResident Rockstar
Hi Anonymous
See if this meets your requirement.
Copy the full script to a new blank query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsnPTSzJV9JRCktNB5JGJiDCwMgQSBkqxergVGAEUmCAT4UxWAWaEv+idAWIMkMjhE0WhBSBKEuwouCSosTypNSiokqgkFtRaWYJSAWSMhPCykAuM1WKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Category = _t, Aisle = _t, Year = _t, Price = _t]), ChType = Table.TransformColumnTypes(Source,{{"Aisle", Int64.Type}, {"Year", Int64.Type}, {"Price", Int64.Type}}), GroupRows = Table.Group( ChType, {"Name", "Category", "Aisle"}, { { "t", (x) => Table.AddColumn( Table.Sort(x, {{"Year", Order.Ascending}}), "r", (y) => [ Current = y[Price], YearNeg1 = Table.Sort(x, {{"Year", Order.Ascending}}){[Year = y[Year] - 1]}?[Price]?, YearNeg2 = Table.Sort(x, {{"Year", Order.Ascending}}){[Year = y[Year] - 2]}?[Price]?, DiffToPrevYear = Current - YearNeg1 ?? "NA", DiffToYearBefore = YearNeg1 - YearNeg2 ?? "NA" ] ), type table [Name = nullable text, Category = nullable text, Aisle = nullable number, Year = nullable number, Price = nullable number,r = nullable record] } } ), ExpandTable = Table.ExpandTableColumn(GroupRows, "t", {"Price", "r"}, {"Price", "r"}), ExpandRecord = Table.ExpandRecordColumn(ExpandTable, "r", {"DiffToPrevYear", "DiffToYearBefore"}, {"DiffToPrevYear", "DiffToYearBefore"}) in ExpandRecord