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
