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

     

     

CNENFRNL's avatar
CNENFRNL
Community Champion
4 years ago

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's avatar
    JP8991
    Kudo Commander
    4 years ago

    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's avatar
      ronrsnfld
      Super User
      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