Forum Discussion
Help with complex problem needing done in M, DAX or SQL
Hi,
I need to create a column called Current Pot, whose output says 'Yes' or 'No'.
I have 3 columns that can be used to help create this: TraineeID (unique number given to each trainee when they start a course), Pot (the pot number they are on - this starts at 1 when they've joined the course, goes upto 2 if they go on a break or withdraw, 3 if they return from a break etc... the highest we currently have is 10, but this could be higher in future), and URN (concatenation of TraineeID and Pot without a space or punctuation - done in SQL).
In order to state it's their Current Pot, I need to select only the highest Pot of each TraineeID, which can change with time.
Does anyone know how I could go about this, please?
Any help would be greatly appreciated - thank you for reading.
** Please let me know if I need to try and explain this better, or you require more information.
GroupBy Trainee and Aggregate by Max of Pot
Then Join with ungrouped table and transform the non-empty tables to Yes
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WijBU0lEyVIrVgTKNIEwjhKgRQtQYIWqMKmqMMMEYIWoCYZogtJmgWoGkzVQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Trainee = _t, Pot = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Trainee", type text}, {"Pot", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "URN", each [Trainee]&Number.ToText([Pot],"00"), type text), #"Grouped Rows" = Table.Group(#"Added Custom", {"Trainee"}, {{"Max Pot", each List.Max([Pot]), type nullable number}}), #"Merge" = Table.NestedJoin(#"Added Custom",{"Trainee","Pot"}, #"Grouped Rows",{"Trainee","Max Pot"},"Is Max Pot", JoinKind.LeftOuter), #"Is Max" = Table.TransformColumns(#"Merge",{"Is Max Pot", each if Table.IsEmpty(_) then null else "Yes", type nullable text}) in #"Is Max"Before
After
2 Replies
GroupBy Trainee and Aggregate by Max of Pot
Then Join with ungrouped table and transform the non-empty tables to Yes
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WijBU0lEyVIrVgTKNIEwjhKgRQtQYIWqMKmqMMMEYIWoCYZogtJmgWoGkzVQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Trainee = _t, Pot = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Trainee", type text}, {"Pot", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "URN", each [Trainee]&Number.ToText([Pot],"00"), type text), #"Grouped Rows" = Table.Group(#"Added Custom", {"Trainee"}, {{"Max Pot", each List.Max([Pot]), type nullable number}}), #"Merge" = Table.NestedJoin(#"Added Custom",{"Trainee","Pot"}, #"Grouped Rows",{"Trainee","Max Pot"},"Is Max Pot", JoinKind.LeftOuter), #"Is Max" = Table.TransformColumns(#"Merge",{"Is Max Pot", each if Table.IsEmpty(_) then null else "Yes", type nullable text}) in #"Is Max"Before
After
Hey,
Thank you so much for your help.
With a couple of very minor tweaks it did the job:
#"Grouped Rows" = Table.Group(#"Added Custom89", {"TRAINEEID"}, {{"Max Pot", each List.Max([Pot]), type nullable number}}),
#"Merge" = Table.NestedJoin(#"Added Custom89",{"TRAINEEID","Pot"}, #"Grouped Rows",{"TRAINEEID","Max Pot"},"Is Max Pot", JoinKind.LeftOuter),
#"Expanded Is Max Pot" = Table.ExpandTableColumn(Merge, "Is Max Pot", {"Max Pot"}, {"Max Pot"}),
#"Added Custom92" = Table.AddColumn(#"Expanded Is Max Pot", "Current Pot", each if [Max Pot] = null then "No" else "Yes")
in
#"Added Custom92"Thanks again.
