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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Custom list sorting for duplicate values

Hi all, 

 

For example, I have this data from a review process: 

IDRating
57795A
57795Pass
57795Pass
57795A

 

What I want to do is have a single result returned for ID 57795 of 'A' (other alternatives are B, C, Pass and Fail) - there's quite a few records and I wondered if there was a Power Query that I could use that would look at the ID then the ratings column, and then provide the highest rating for each ID once. 

 

Thanks!

7 REPLIES 7
SamInogic
Super User
Super User

Hi,

 

Im a bit unclear about Your requirement but It seems you are Looking For a single row for Id and all the ratings of the Id in the same row,

If this is your Requirement then try this out ,

This is my Sample Table,

SamInogic_0-1680181905830.png

 

Use group by as in below Screenshot,

SamInogic_5-1680182095154.png

 


There will be a error,

SamInogic_2-1680181984209.png


To solve this us below Expression

= Table.Group(#"Changed Type", {"ID"}, {{"Result", each Text.Combine([Rating],","), type nullable

 

SamInogic_3-1680182013623.png

 

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/
Anonymous
Not applicable

Thanks - very useful, to confirm I would just want the highest rating returned, but this is a useful start!

Hi @Anonymous , 

 

If you want the highest Ratings so instead of Combining, you need to Use Max of ratings in Group by as in Screenshot,

 

SamInogic_2-1680237182665.png

 

SamInogic_4-1680237236511.png


Note that we are considering that your Ratings is Numerical column.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/
JohnShepherdAPD
Helper II
Helper II

Hi this example should give you what you want, however, if you want a different order of ratings then add an integer column for every rating and group by that

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU3tzRV0lFyVIrVQfACEouL8QtA1RsbGZoAeU4oPLfEzByl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Rating = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Rating", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Rating", each List.Min([Rating]), type nullable text}})
in
    #"Grouped Rows"

 

Anonymous
Not applicable

Thanks for this - looks good, although I'll need to look at the code so I can understand it myself (it's really appreciated that you've provided this, I just need to understand it so I can apply it to similar situations in future!).

ToddChitt
Super User
Super User

Think how you might do this in real life: You would GROUP BY the ID column and take the MIN of the Rating column, or whatever aggregation gets you A values instead of B. Create a DAX (or Power Query) table that does that aggregation.

If you sort your distint list of Ratings, the list would be, in alphabetical order, A, B, D, Fail, Pass (because the F in Fail comess before the P in Pass). If you want Pass to come before Fail, then you should probably convert the letters to numbers: A=1, B=2, C=3, Pass=4, Fail=5, and get the MIN of the number.




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Anonymous
Not applicable

Thanks for this guidance - it's helpful to have someone simply explain to my what I'm trying to do in terms that I can apply to PowerBI! I had hit a wall and had no one to speak with, but the way you've described this is really useful. 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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