[Power Query] Get max/min of group of rows without grouping?

[olimilo]

I'm aware of getting the min/max of a column based on an identifier by grouping the rows according to the identifier column, but is there a way to do this without needing to group the rows according to the identifier column in Power Query?

For example, in the table below, to get the min/max ModifiedDate value, I have to group them according to the ID column and do an aggregate. For a small dataset, this is acceptable but when I reach 100k+, it seems to tank performance.

ID		ModifiedDate
1001453455		12/30/2022 1:17:24 AM
1001453455		12/29/2022 9:55:49 PM
1001495147		8/8/2022 12:20:10 PM
1001495147		7/28/2022 5:19:42 PM
1001507515		7/26/2022 1:36:43 PM
1001507515		8/10/2022 12:08:26 PM

[Ashish_Mathur]

Hi,

If you are interested, this can be solved with a calculated column formula in DAX as well.

[v-xinruzhu-msft · Accepted solution]

Hi @olimilo 

You can create a custom column and input the following code

=List.Max(Table.SelectRows(the last step name(e.g #"Changed Type"),(x)=>x[ID]=[ID])[ModifiedDate])

Output

and you can refer to the attachment below.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.