Create column that identifies Row with highest value for an ID#

[Anonymous]

Hi All!

So this is the data sample:

I need to create a new column that for each ID#, checks which is the highest value in Sprint # (example, for ID# 1 would be 2, for ID#2 would be 3), and if it is the highest value for that ID#, use the word "Current Sprint", and if its not "Past Sprint".

The Result would look like this:

I added an index because I usually sort this out with the EARLIER function for these, but for some reason I am not finding the right approach.

Regards,

[m_alireza · Accepted solution]

Hi @Anonymous ,

You can do this without the index column. Instead, use the Group by Function in Power Query. 
1. Open Power Query Editor
2. Click on Group by under Transform tab
3. In the pop up box, select Group by ID, and in 'advanced',  add a new column called "MaxSprint#" that takes Sprint # as an operation and another column called "All" that takes All Rows as an operation.
4.Expand the All column to retrieve Sprint# and Sprint Name
5. Add a conditional column that states if MaxSprint# = Sprint#, "Current Sprint", otherwise "Past Sprint"
6. Remove MaxSprint# column as no longer required.

see query below; copy and paste this in your advanced editor and amend as needed:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAePggqLMvBIFQz1DpVgdiLARsrARWNgIu2oj3KqNkYWNwcLGqIYYQQ0xwS1shCwMNDsWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID#" = _t, #"Sprint #" = _t, #"Sprint Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID#", Int64.Type}, {"Sprint #", Int64.Type}, {"Sprint Name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID#"}, {{"MaxSprint#", each List.Max([#"Sprint #"]), type nullable number}, {"All", each _, type table [#"ID#"=nullable number, #"Sprint #"=nullable number, Sprint Name=nullable text]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Sprint #", "Sprint Name"}, {"Sprint #", "Sprint Name"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded All", "Custom", each if [#"Sprint #"] = [#"MaxSprint#"] then "Current Sprint" else "Past Sprint"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"MaxSprint#"})
in
    #"Removed Columns"

Sample output: