Help with complex problem needing done in M, DAX or SQL

[bitofanewb]

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.

[ronrsnfld · Accepted solution]

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

[bitofanewb]

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.