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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JB-Nichols
Regular Visitor

Highest unique value with filters

Hi, 

 

I have a Table that I'd ideally like to transform so that it only shows the highest score of the submitted responses on a form. 

 

Hopefully below explains it easier with Title, User and Score. Ideally we would have End Result 1. 

 

JBNichols_0-1667385055796.png

 

 

How would this be possible? If I can do this by transofrming the data what would I need to do?

 

Thanks

 

 

 

1 ACCEPTED SOLUTION

You will need to add another operation in the group by called "AllRows", and then expand your other columns along with the "Score" column. Then you can do a condition that says to only keep all the columns in which score = max score.

The query below does all the steps for you. Copy and paste it in your advanced editor and amend as needed to match your columns:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcssvyjVU0lECYXMgNjYxMgFSiUnJSrE6qNKGBkDCCKjA2ATETUxJgysxgioxA9FGYOn0tLSUdBQFIGwKUmBsYgxip6SnpWAogFgCVABxR0paOsiaWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Title = _t, User = _t, Score = _t, Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Score", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Title", "User"}, {{"maxscore", each List.Max([Score]), type nullable number}, {"AllRows", each _, type table [Title=nullable text, User=nullable text, Score=nullable number, Column1=nullable text, Column2=nullable text]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Score", "Column1", "Column2"}, {"Score", "Column1", "Column2"}),
    Custom1 = Table.SelectRows(#"Expanded All",each [maxscore]=[Score])
in
    Custom1

 


sample output:
sampleoutput.png
You can then just delete either the maxscore column or the score column in the end so you dont have two duplicated columns

View solution in original post

3 REPLIES 3
m_alireza
Solution Specialist
Solution Specialist

Hi @JB-Nichols , 

In Power Query, you can click on "Group by" under the Transform tab. 

In the pop up, click advanced. You can group by Title and User, and add a new column that gets Max Score as seen in pic below:

groupbyscore.png

Sample output:
groupbyscoreoutput.png

Thanks. How do I do this and then keep other columns in view? Title, User, Score is the start and then additionslly there is Column 1, Column 2 etc... 

You will need to add another operation in the group by called "AllRows", and then expand your other columns along with the "Score" column. Then you can do a condition that says to only keep all the columns in which score = max score.

The query below does all the steps for you. Copy and paste it in your advanced editor and amend as needed to match your columns:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcssvyjVU0lECYXMgNjYxMgFSiUnJSrE6qNKGBkDCCKjA2ATETUxJgysxgioxA9FGYOn0tLSUdBQFIGwKUmBsYgxip6SnpWAogFgCVABxR0paOsiaWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Title = _t, User = _t, Score = _t, Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Score", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Title", "User"}, {{"maxscore", each List.Max([Score]), type nullable number}, {"AllRows", each _, type table [Title=nullable text, User=nullable text, Score=nullable number, Column1=nullable text, Column2=nullable text]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Score", "Column1", "Column2"}, {"Score", "Column1", "Column2"}),
    Custom1 = Table.SelectRows(#"Expanded All",each [maxscore]=[Score])
in
    Custom1

 


sample output:
sampleoutput.png
You can then just delete either the maxscore column or the score column in the end so you dont have two duplicated columns

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.