Add column showing max value from another column

[rachaelwalker]

I am in power query creating custom columns. I have an table that is grouped by opportunity number. I successfully added a column that shows the date from previous row. Now, I need to add a column with the MAX value of [Index]. I am not sure why I am getting errors. I have tried MAX and List.Max using similar code below. 

Added date from previous row = success

= Table.AddColumn(#"Grouped Rows", "PrevDateTable", each let 
AllDataTable = [FullTable],
PrevRowValue = 
  Table.AddColumn(
   AllDataTable, "Date Previous UTC", 
   each try AllDataTable [Date Updated UTC] { [Index] - 1}    otherwise [Date Became Lead UTC]
   )
     in 
        PrevRowValue)

Here is what I am trying to accomplish. Is there something I can add onto the code above to avoid combine another table. 

**there are multiple opportunties but this is only showing the one. So I need MAX index for each opp. 

Table

Index	Opportunity Number	audit_token	Stage Current	Stage Audit Value	Date Updated UTC	Date Previous UTC	Index Max
0	7994	old_value	Qualified Prospect	Strong Upside	5/6/2022 14:17	1/25/2022 16:50	9
1	7994	new_value	Qualified Prospect	Quoted	5/6/2022 14:17	5/6/2022 14:17	9
2	7994	old_value	Qualified Prospect	Quoted	5/6/2022 14:17	5/6/2022 14:17	9
3	7994	new_value	Qualified Prospect	Qualified Prospect	5/6/2022 14:17	5/6/2022 14:17	9
4	7994	old_value	Qualified Prospect	Qualified Prospect	6/17/2022 13:47	5/6/2022 14:17	9
5	7994	new_value	Qualified Prospect	Strong Upside	6/17/2022 13:47	6/17/2022 13:47	9
6	7994	old_value	Qualified Prospect	Strong Upside	8/3/2022 1:18	6/17/2022 13:47	9
7	7994	new_value	Qualified Prospect	Quoted	8/3/2022 1:18	8/3/2022 1:18	9
8	7994	old_value	Qualified Prospect	Quoted	8/3/2022 1:18	8/3/2022 1:18	9
9	7994	new_value	Qualified Prospect	Qualified Prospect	8/3/2022 1:18	8/3/2022 1:18	9

[edhans]

Hi, you want to use Table.Max.

Table.Max([All Rows],"Value")[Value]

 The Table.Max([All Rows],"Value") function returns 1 record based on the max value of the "Value" field. The [Value] at the end pulls the actual value. Full code sample

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCshJLEnLL8pV0lFyzkksLgbSYYk5palKsTrRSq5BzkA+hDSEiSQWw8WMYGLBrm5wQWOwYCKQlVickgakTMACSQgBU7BAMkLADCyQghAwV4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Class"}, {{"All Rows", each _, type table [Platform=nullable text, Class=nullable text, Value=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Max([All Rows],"Value")[Value])
in
    #"Added Custom"

 How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model. 

[rachaelwalker]

Thank you for taking the time to help me. For the example you provided, I need the MAX index within the grouped by row so I can find the MAX index for each Opportunity. Please see screenshot. I tried using Table.Max and was able to get a little further but still receiving an error.

Here is a screenshot of attempting Table.Max

= Table.AddColumn(#"Added PrevDateTable", "MaxIndex", each let 
AllDataTable = [PrevDateTable],
MaxIndex = 
  Table.AddColumn(
   AllDataTable, "Index Max", 
   each Table.Max(AllDataTable, "Index MAX")[Index]
   )
     in 
        MaxIndex)

After that step, I combine all the tables. Im sure there is an easier way to go about this. I am still learning

[edhans]

You are going to need to provide some code samples or what the errors are. You just show the word "error" which may be a problem with how you are using it, or you may be missing a comma. No idea. 

Paste your data into the ENTER DATA feature of Excel or Power BI, do your code through the error place, and paste that code.

I cannot use your code sample above because I have no clue what the code through PrevDateTable did.

[rachaelwalker · Accepted solution]

A quick work around was to create a separate SQL query and group by Opportunity and MAX index then merged the queries. Not sure if it's the most efficient but I am now getting the data I need. Thank you for taking the time.