Forum Discussion
SelectRows apply to same column cause cyclic reference
Hi
I have a table.
| ID | Exam | Score |
1 | A | 50 |
| 2 | A | 60 |
| 3 | A | 40 |
| 4 | B | 30 |
| 5 | B | 20 |
| 6 | B | 40 |
Then I hope to add to prepare like below column for using post analysis.
| ID | Exam | Score | ExamBest |
1 | A | 50 | 60 |
| 2 | A | 60 | 60 |
| 3 | A | 40 | 60 |
| 4 | B | 30 | 40 |
| 5 | B | 20 | 40 |
| 6 | B | 40 | 40 |
So I write below expression.
= Table.AddColumn(Filtered, "Custom", List.Max(Table.SelectRows(Scores, (r)=>[Exam]=r[Exam])[Score]))
But this expression cause expression.error a cyclic reference was encountered , How can I avoid?
# Sorry for my poor English, plaese reply me any correction.
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"
5 Replies
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"Hi, m_alireza ,
I have similar issue and using Table.Group function and still facing issue.I am looking to get the Average of forecast coloumn for Current Week to 6th Week Group by InternalID and I have tried multiple attempts to format the function.
1)
let MINDATE = Date.WeekOfYear(DateTime.LocalNow()), Maxdate = MINDATE + 5, Source = #"Demand Fcst Raw Outputs", FilteredTable = Table.Group( Table.SelectRows(Source, each [Week Number] >= MINDATE and [Week Number] <= Maxdate), {"Internal ID"}, {{"TotalForecast", each List.Sum([Forecast]), type number}} ), FinalTable = Table.AddColumn(FilteredTable, "Test", each [TotalForecast] / 6) in FinalTable
2) let
FilteredTable = Table.Group(#"Demand Fcst Raw Outputs (2)", [Internal ID],List.Sum[Forecast],[Week Number]=Date.WeekOfYear(DateTime.LocalNow()) and [Week Number]<=Date.WeekOfYear(DateTime.LocalNow())+5),
FinalTable = Table.AddColumn(FilteredTable, "Test", each [TotalForecast] / 6)
in
FinalTable
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.
