[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.

[jajangjaras]

the solution works, but the performance of my power query is horrible after I appplied the solution. I mean I could not load the table to excel sheet because the excel stop responding before it can finish. Do you have any suggestion to solve this issue?

Ps.

1. data source is from sharepoint

2. the table has about 4,xxx rows and about 30 columns (after removed the uncessary columns).

3. it also takes a very long time to open the power query after I applied the solution.