SelectRows apply to same column cause cyclic reference

[Thoughtknotseer]

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.

[Thoughtknotseer]

@m_alireza 

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.

[wdx223_Daniel]

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]))

[Thoughtknotseer]

Thank you! I keep study to understand.

[m_alireza · Accepted solution]

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"

[Dwisha_S]

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