Assigning a value to an ID based on a column with multiple values

Hi dh123 

It  can also work, the logic is to sort the value by descending , then take the first value, the first value will be the max value

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi dh123, try this one:

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJJLUvNUTDUUYAwjGAMY6VYnWglI7gKYwwVhmAVxnAVJnAVCCVwhplSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Qualifications = _t]),
    Ad_LevelNumber = Table.AddColumn(Source, "Level Number", each List.Transform(Text.Split([Qualifications], ","), (x)=> Number.From(Text.Select(Text.From(x), {"0".."9"})))  , type list),
    Ad_HighestNumberPosition = Table.AddColumn(Ad_LevelNumber, "Highest Number Position", each List.PositionOf([Level Number], List.Max([Level Number])), type number),
    Ad_HighestLevelOfPosition = Table.AddColumn(Ad_HighestNumberPosition, "Highest level of qualification", each List.Transform(Text.Split([Qualifications], ","), Text.Trim){[Highest Number Position]}, type text),
    #"Removed Columns" = Table.RemoveColumns(Ad_HighestLevelOfPosition,{"Level Number", "Highest Number Position"})
in
    #"Removed Columns"