Forum Discussion
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 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
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.
10 Replies
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.Nice solution, thanks a lot!
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
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
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.
Hi, I Just Tried again, This time no error but resulted in an empty Table
Please assist
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
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]
