Column that Calculates Previous Step

[JP8991]

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 being the end result.

ID	Rank	Step	Previous Step
12345	1	Step 1	null
12345	2	Step 2	Step 1
12345	3	Step 3	Step 2
12345	4	Step 4	Step 3
12345	5	Step 5	Step 4
12345	6	Step 6	Step 5
12345	7	Step 7	Step 6
12345	8	Step 8	Step 7
12345	9	Step 9	Step 8

Any help would be greatly appreciated.

[CNENFRNL]

Column Rank is redundant. Mount a slighted-tweaked column to the original table, that's enough,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lEKLkktUDBUitVBEzLCFDLGFDLBFDLFFDLDFDLHFLLAFLJUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Step = _t]),
    Cols = Table.ToColumns(Source),
    #"Added Column" = Table.FromColumns(Cols & {{null} & List.RemoveLastN(List.Last(Cols),1)}, Table.ColumnNames(Source) & {"Prev"})
in
    #"Added Column"

[JP8991]

Close but I need it by ID.

Please see the below code, you will notice I added a new ID, which should show null for Step 2 and Step 2 for Step 6 in the Prev column.

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]),
    Cols = Table.ToColumns(Source),
    #"Added Column" = Table.FromColumns(Cols & {{null} & List.RemoveLastN(List.Last(Cols),1)}, Table.ColumnNames(Source) & {"Prev"})
in
    #"Added Column"

[ronrsnfld · Accepted solution]

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]

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

[serpiva64]

Hi,

this are the passages you can apply to obtain your result:

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]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Rank", Int64.Type}, {"Step", type text}, {"Previous Step", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Rank]-1),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom.1", each if [Custom] = "0" then null else "Step "& [Custom]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Custom.1", type text}})
in
#"Changed Type2"