Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Retrieve previous row value based on 2 columns

Hi, I have a table consisting of columns  Product Type Metric Year Each product undergoes checking twice Review and FInal. A Metric is given to it.  Sample data Product Type Metric...
Need Help
  • BA_Pete's avatar
    BA_Pete
    2 years ago

    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

     

  • AlienSx's avatar
    AlienSx
    2 years ago

    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"})
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi AlienSx 

 

Can you please explain the steps in simple english.

 

Cheers

 

CheenuSing

AlienSx's avatar
AlienSx
Super User
2 years ago

Anonymous Can't do step by step. I'll explain the idea.

f = (x) => is custom function (it's not a step) that takes a table as an argument. It takes a column with metric, "shifts it down" by one item (cuts the tail of the list and adds null as first item: {null} & List.RemoveLastN(x[Metric], 1)) so that we have a list with previous metrics values. Then we simply split table x to a list of columns (Table.ToColumns), add a list with shifted metrics values to it and transform this updated list of columns back to table (Table.FromColumns).

Function f must be applied to a group of rows with the same  values of Product and Type. That what Table.Group does. Then we simply create new aggregated column pm, sort each table by year and apply our transformation f . Expand some columns from transformed table in the end (Table.ExpandTableColumn).