Forum Discussion
Solved
Add 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 wi...
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.
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.