Retrieve previous row value based on 2 columns

Hi Anonymous ,

Here's one way to do it using two offset Index columns and merging the table on itself:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddK9DoQgEATgd6G28BAQSmNijvauNBZXWJhcLPX1NcL9ADMFofiSnWWXcRSdqMSwrK/3eTfnkfVNiamCoBkYBm0BKoBl4AjImkHzhce8LfOOQnJxTP5icgk5/b3sWVExVCyT2EEiOoj8CX4qoljP00V7sAVDxTJBOUnfPhmqLspl9Kn3ZPNGoqkYKi0VS8Ux4V3H/3MJ3h4kB6gcEPir0wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Type = _t, Metric = _t, Year = _t]),

    sortProductTypeYear = Table.Sort(Source,{{"Product", Order.Ascending}, {"Type", Order.Ascending}, {"Year", Order.Ascending}}),
    addIndex1 = Table.AddIndexColumn(sortProductTypeYear, "Index1", 1, 1, Int64.Type),
    addIndex0 = Table.AddIndexColumn(addIndex1, "Index0", 0, 1, Int64.Type),
    mergeOnSelf = Table.NestedJoin(addIndex0, {"Product", "Type", "Index0"}, addIndex0, {"Product", "Type", "Index1"}, "addIndex", JoinKind.LeftOuter),
    expandPreviousMetric = Table.ExpandTableColumn(mergeOnSelf, "addIndex", {"Metric"}, {"PreviousMetric"}),
    remOthCols = Table.SelectColumns(expandPreviousMetric,{"Product", "Type", "Metric", "Year", "PreviousMetric"})
in
    remOthCols

To get this output:

Pete

hello, Anonymous 

    s = your_table,
    f = (x) => 
        [a = Table.ToColumns(x),
        b = Table.FromColumns(a & {{null} & List.RemoveLastN(x[Metric], 1)}, Table.ColumnNames(x) & {"Previous Metric"})][b],
    g = Table.Group(s, {"Product", "Type"}, {{"pm", (x) => f(Table.Sort(x, "Year"))}}),
    z = Table.ExpandTableColumn(g, "pm", {"Metric", "Year", "Previous Metric"})