Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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!
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
Sorry about that!!
Try:
Table.AddColumn(LastStep, "NewColumnName", each Table.SelectRows([Details], each [MaxDate] = LastStep[Max_Date] and [RevOrder] = LastStep[Max_RevOrder]))
Hi, I Just Tried again, This time no error but resulted in an empty Table
Please assist
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.