Forum Discussion
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...
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
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"
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