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

[dh123]

Hi everyone,

Looking for some help.

I want to allocate a single value (highest level of qualification) to a unique ID using another column with multiple values (i.e. level 1, level 2, level 3)

ID	Qualifications
1	Level 1, Level 2, Level 3

I have split by delimiter into rows as below, but I’m not sure if this will help.

ID	Qualifications
1	Level 1
1	Level 2
1	Level 3

I am aiming for something like this.

ID	Qualifications	Highest level of qualification
1	Level 1, Level 2, Level 3	Level 3

If the above is not possible I have another table that I can assign the value to instead based on ID but I’m not sure how to do this.

Any suggestions welcome

[Anonymous · Accepted solution]

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.

[dufoq3 · Accepted solution]

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"

[Anonymous]

Hi @dh123 

Create a custom column and try the following code.

List.First(List.Sort(Text.Split(Text.Replace([Qualifications]," ",""),","),Order.Descending))

Output

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.

[dh123]

Hi,

Thank you for responding - the issue with this formula is that it only takes the last value in the column but the values are not always in order - it could be level 2, level 3, level 1

[Anonymous · Accepted solution]

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.

[Vijay_A_Verma]

Put this formula in a custom column

List.Last(List.Sort(Text.Split([Qualifications],", "), (x)=> Number.From(List.Last(Text.Split(x," ")))))

[dh123]

This seems to work but only when it can recognise a number, I'm getting some errors as not all the qualifications will have a numbers.

DataFormat.Error: We couldn't convert to Number.
Details:
Course