Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Calculate row difference based on conditions

Looking for some help to implement this in power query.

 

Thanks!

1 Reply

  • m_dekorte's avatar
    m_dekorte
    Resident 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