Add List.Max within Group

[soni27]

1) I would like to select the row of Max of MaxDate Column and Max of RevOrder Column

2) I used Group and Trying to Add using List.Max function, to find the Max of RevOrder within Group

3) But there is error

4)  Input: 

NS_DOC_NO	MaxDate	RevOrder
TH-ART-3D-GEN-GLV-PIP-GA-0001	43889.6569	615
TH-ART-3D-GEN-GLV-PIP-GA-0001	43979.3233	616
TH-ART-3D-GEN-GLV-PIP-GA-0001	43937.4705	616
TH-ART-3D-GEN-GLV-PIP-GA-0002	43899.6569	615
TH-ART-3D-GEN-GLV-PIP-GA-0002	43989.3233	620
TH-ART-3D-GEN-GLV-PIP-GA-0002	43977.4705	620

Output:

NS_DOC_NO	MaxDate	RevOrder
TH-ART-3D-GEN-GLV-PIP-GA-0001	43979.32326	616
TH-ART-3D-GEN-GLV-PIP-GA-0002	43977.47049	620

= Table.AddColumn(Step1, "Custom", each Table.AddColumn([AllRows], "Max", each List.Max(Step1[AllRows][RevOrder])))

Please assist me 

[v-jingzhang · Accepted solution]

Hi @soni27 

You can first group by column NS_DOC_NO and get all rows into a table column. Then filter the table column with below method in images. I add two custom columns to get the filtered tables. At last, keep the last filtered table column and remove other columns. Expand the filtered table column to get the rows you want.

Download the attachment for details.

Below are M codes in Advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY7BCsIwDIbfpeeltM3arsfhpArajjm8jDEGDjyPIT6+cYIDL/by50/IB1/XsXAZqrgbQmQZO4/Palwmas30iPNtmqnG5U6zzzrWHqBsWsAK/D6AP12hPtbgSxBCSPrMsSgcN9o4WozUlDIRdNZxVIgraCgxFUTLcyv0F9R/QfVRdT+qKhF0xaaqxPuUCtpNdQUN6/sX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, Column1 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"NS_DOC_NO", type text}, {"MaxDate", type number}, {"RevOrder", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"NS_DOC_NO"}, {{"All", each _, type table [NS_DOC_NO=nullable text, MaxDate=nullable number, RevOrder=nullable number, Other=nullable text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "filter1", each let
_maxRevOrder = List.Max([All][RevOrder])  
in 
Table.SelectRows([All], each [RevOrder] = _maxRevOrder)),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "filter2", each let
_maxDate = List.Max([filter1][MaxDate])
in
Table.SelectRows([filter1], each [MaxDate] = _maxDate)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All", "filter1"}),
    #"Expanded filter2" = Table.ExpandTableColumn(#"Removed Columns", "filter2", {"MaxDate", "RevOrder", "Other"}, {"MaxDate", "RevOrder", "Other"})
in
    #"Expanded filter2"

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[jdd90]

Nice solution, thanks a lot!

[watkinnc]

OK, here is how to get the Max Date and Max RevOrder for each DOC_NO--not sure if this is what you need. THe source is your table pasted into an Excel workbook.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"NS_DOC_NO"}, {{"Details", each _, type table [NS_DOC_NO=text, MaxDate=number, RevOrder=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "MaxDate", each List.Max([Details][MaxDate]), type number),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Max_RevOrder", each List.Max([Details][RevOrder]), type number)
in
#"Added Custom1"

--Nate

[soni27]

Further to Your suggestion, we are getting closer now

The objective is to filter the rows with Max RevOrder first, then Select the Rows with that value and then select the rows with Max MaxDate and then remove duplicate for each NS_DOC_NO

• How can we bring the Max_RevOrder as a column inside the [Details] Table
• Then Select the rows of [Details] Table with [Max_RevOrder]
• Add Max_MaxDate as a column inside the [Details] Table
• Then Select the rows of [Details] Table with [Max_MaxDate]
• Then remove Duplicate based on [NS_DOC_No], [[Max_RevOrder], [Max_MaxDate]
• The remove [Max_RevOrder] and [Max_MaxDate]

[watkinnc]

Sorry about that!!

Try:

Table.AddColumn(LastStep, "NewColumnName", each Table.SelectRows([Details], each [MaxDate] = LastStep[Max_Date] and [RevOrder] = LastStep[Max_RevOrder]))

[soni27]

Hi, I Just Tried again,  This time no error but resulted in an empty Table

Please assist

[watkinnc]

Ok now I get it. Let's say you used the GUI to group on NS_DOC_NO, and used Max for MaxDate and RevOrder, and named them "Max_Date" and "Max-RevOrder" also an All Rows column named "Details". If your last step was named "LastStep", then:

Table.SelectRows(LastStep, each [Details][MaxDate] = [Max_Date] and [Details][RevOrder] = [Max_RevOrder])

Then you can expand the column and your rows are selected. 
Some folks are more comfortable adding a new table column, and if so, that's:

Table.AddColumn(LastStep, "NewColumnName", each Table.SelectRows([Details], each [MaxDate] = [Max_Date] and [RevOrder] = [Max_RevOrder])

--Nate

[soni27]

Thanks, I tried using UI and Created the additional columns

then I used Table.SelectRows(LastStep, each [Details][MaxDate] = [Max_Date] and [Details][RevOrder] = [Max_RevOrder]),  It resulted in empty Table

I am tried to create an additional column in grouped Table,  it didn;t worked.

Table.AddColumn(LastStep, "NewColumnName", each Table.SelectRows([Details], each [MaxDate] = [Max_Date] and [RevOrder] = [Max_RevOrder])

Please assist to resolve this.

[watkinnc]

I would use the actual Group function in the GUI, under the Transform Tab. Group by NS_DOC_NO, and then choose Max as the aggregations for MaxDate and RevOrder.

--Nate

[soni27]

Thanks for your reply dear

My Idea is to get the Max of MaxDate and Get Max of RevOrder and the use Table.Select rows function to get the complete row within group

The reason is there are any columns in the data, and I wanted to select the row that has Max of RevOrder and Max of MaxDate

Please assist me to write M Code for that