Forum Discussion
Assigning a value to an ID based on a column with multiple values
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
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"
6 Replies
Put this formula in a custom column
List.Last(List.Sort(Text.Split([Qualifications],", "), (x)=> Number.From(List.Last(Text.Split(x," ")))))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
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.
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
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"
