Forum Discussion
SelectRows apply to same column cause cyclic reference
Hi Thoughtknotseer ,
You can use the Group by Function in Power Query to achieve your desired result.
Copy the query below in advanced editor to see steps:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYlMDpVidaCUjKNcMwjWGck0gXBMg0wmIjSFcUyjXCMI1g3JBimMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Exam = _t, Score = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Exam", type text}, {"Score", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Exam"}, {{"MaxScore", each List.Max([Score]), type nullable number}, {"AllRows", each _, type table [ID=nullable number, Exam=nullable text, Score=nullable number]}}), #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"ID", "Score"}, {"ID", "Score"}) in #"Expanded AllRows"
Really Thank you!! Table.Group is what I looking for.
Sorry but can I ask additional question?
Now I hope to query another column. Example table is below.
| ID | Exam | ExamDate | Score |
1 | A | 1 | 50 |
| 2 | A | 2 | 60 |
| 3 | A | 3 | 40 |
| 4 | B | 1 | 30 |
| 5 | B | 2 | 20 |
| 6 | B | 3 | 40 |
I also hope to get below column.
| ID | Exam | ExamDate | Score | BestScore | BestDate |
1 | A | 1 | 50 | 60 | 2 |
| 2 | A | 2 | 60 | 60 | 2 |
| 3 | A | 3 | 40 | 60 | 2 |
| 4 | B | 1 | 30 | 40 | 3 |
| 5 | B | 2 | 20 | 40 | 3 |
| 6 | B | 3 | 40 | 40 | 3 |
I was confused I suddenly ordered to use PQ at work. Really thank you for your support.
NewStep= let GpMx=Table.Buffer(Table.Group(PreviousStepName,"Exam",{"n",each List.MaxN(Table.ToRows([[Score],[ExamDate]]),1,each _{0}){0}})) in #table(Table.ColumnNames(PreviousStepName)&{"BestScore","BestDate"},List.Transform(Table.ToRows(PreviousStepName),each _&GpMx{[Exam=_{1}]}[n]))
Thank you! I keep study to understand.
