Forum Discussion
Resolver IIIAdd column showing max value from another column
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 |
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.
4 Replies
- edhans
Community Champion
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.
