Forum Discussion

soni27's avatar
soni27
Icon for Helper I rankHelper I
5 years ago
Solved

Add List.Max within Group

  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 t...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    soni27
    Icon for Helper I rankHelper I
    5 years ago

    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]