Forum Discussion

JP8991's avatar
JP8991
Kudo Commander
4 years ago
Solved

Column that Calculates Previous Step

Hello All,   I would like to create a calculated column in Power Query that calculates the previous step based on an ID. Below is an example of what I am after with the "Previous Step" column be...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    4 years ago

    If that's what you want, which seems different from your initial example, just Group by ID and then add the shifted column as a custom aggregation within the Table.Group function:

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lEKLkktUDBUitVBEzLCFDLGFDLBFDLFFDLDFDLHFLLAFLIEC5mZW1gaoLoLRQhofCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Step = _t]),
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {
        {"Previous Step", each Table.FromColumns(
            Table.ToColumns(_) & {{null} & List.RemoveLastN([Step],1)},
            type table[ID=Int64.Type,Step=text, Prev Step=text]
        ),type table[ID=Int64.Type,Step=text, Prev Step=text]}
        }),
    #"Expanded Previous Step" = Table.ExpandTableColumn(#"Grouped Rows", "Previous Step", {"Step", "Prev Step"}, {"Step", "Prev Step"})
in
    #"Expanded Previous Step"

     

    Before

    After

     

     

BA_Pete's avatar
BA_Pete
Super User
4 years ago

Hi JP8991 ,

Paste this over the default code in a new blank query to follow the steps I took:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYo7DoAwFMOugt7cpf/2HIxVR7YKMcD9gSFKhkiR7THMh5iyOfPf9vu4tv+cz1o2HW2ADczUR+DIUH0CTgzVZ+DMUH0BLgzVV+DKUH0DbgzVd+DOcM4X", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Rank = _t, Step = _t, #"Previous Step" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Rank", Int64.Type}, {"Step", type text}, {"Previous Step", type text}}),
    #"addRank-1" = Table.AddColumn(chgTypes, "rank-1", each [Rank] - 1),
    mergeOnSelf = Table.NestedJoin(#"addRank-1", {"rank-1"}, #"addRank-1", {"Rank"}, "Added Custom", JoinKind.LeftOuter),
    expandStepCol = Table.ExpandTableColumn(mergeOnSelf, "Added Custom", {"Step"}, {"Step.1"}),
    sortRank = Table.Sort(expandStepCol,{{"Rank", Order.Ascending}})
in
    sortRank

 

Summary:

1) Create [rank-1] column just subtracting 1 from the [Rank] column (assuming this order is what you are basing the 'previous step' evaluation on).

2) Merge table on itself - LEFT OUTER on [rank-1] = [Rank]

3) Expand the [Step] column from the nested tables.

 

This gives me the following output:

 

Pete