Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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 @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
Proud to be a Datanaut!
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.