Forum Discussion
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.
How would this be possible? If I can do this by transofrming the data what would I need to do?
Thanks
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:
You can then just delete either the maxscore column or the score column in the end so you dont have two duplicated columns
3 Replies
- m_alireza
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:
Sample output: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...
- m_alireza
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:
You can then just delete either the maxscore column or the score column in the end so you dont have two duplicated columns
