Forum Discussion
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 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.
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
5 Replies
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"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
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"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 sortRankSummary:
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
