Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
dh123
New Member

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)

 

IDQualifications
1Level 1, Level 2, Level 3

 


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

IDQualifications
1Level 1
1Level 2
1Level 3

 

I am aiming for something like this.

 

IDQualificationsHighest level of qualification
1Level 1, Level 2, Level 3Level 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

2 ACCEPTED SOLUTIONS

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

vxinruzhumsft_0-1708996364280.png

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.

View solution in original post

dufoq3
Super User
Super User

Hi @dh123, try this one:

 

Result:

dufoq3_0-1709216414665.png

 

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"

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

6 REPLIES 6
dufoq3
Super User
Super User

Hi @dh123, try this one:

 

Result:

dufoq3_0-1709216414665.png

 

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"

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

v-xinruzhu-msft
Community Support
Community Support

Hi @dh123 

Create a custom column and try the following code.

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

Output

vxinruzhumsft_0-1708924962953.png

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

vxinruzhumsft_0-1708996364280.png

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
Super User
Super User

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors