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

     

     

serpiva64's avatar
serpiva64
Solution Sage
4 years ago

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"