Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
soni27
Helper I
Helper I

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_NOMaxDateRevOrder
TH-ART-3D-GEN-GLV-PIP-GA-000143889.6569615
TH-ART-3D-GEN-GLV-PIP-GA-000143979.3233616
TH-ART-3D-GEN-GLV-PIP-GA-000143937.4705616
TH-ART-3D-GEN-GLV-PIP-GA-000243899.6569615
TH-ART-3D-GEN-GLV-PIP-GA-000243989.3233620
TH-ART-3D-GEN-GLV-PIP-GA-000243977.4705620

 

Output:

NS_DOC_NOMaxDateRevOrder
TH-ART-3D-GEN-GLV-PIP-GA-000143979.32326616
TH-ART-3D-GEN-GLV-PIP-GA-000243977.47049620

soni27_1-1624805715715.png

 

 

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

 

Please assist me 

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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.

070702.jpg070703.jpg

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.

View solution in original post

10 REPLIES 10
v-jingzhang
Community Support
Community Support

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.

070702.jpg070703.jpg

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!

watkinnc
Super User
Super User

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

 

watkinnc_0-1624851864162.png

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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
Super User
Super User

Sorry about that!!

Try:

 

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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

Please assist

soni27_0-1624843275142.png

 

watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

Thanks, I tried using UI and Created the additional columns

soni27_0-1624838860990.png

 

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

 

soni27_1-1624838932096.png

 

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])

 

soni27_2-1624839113371.png

 

Please assist to resolve this.

 

 

watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors