Creating a custom column to return max value from another column in the same table

Hi drogzy ,

To do this over the whole table you would add something like this to a custom column:

List.Max(previousStepName[Start])

To do it over groups, over each [ID] for example, you will have to group the table, otherwise Power Query has no concept of where you want to limit the operation.

You can do this while maintaining folding as follows:

- Duplicate your query and disable load for this new query.

- Implement the simple group on this new table i.e. Group By [ID], MAX [Start] as aggregate column.

- Merge your grouped query to your original query using LEFT OUTER on original[ID] = grouped[ID].

If you look at the Native Query for your original query now, you will see that a subquery is being sent to the server for the grouped table and everything takes place server-side i.e. the query is fully folded.

Pete

You could do this:
1) sort the table by groups and by date;
2) add an index column {0,1,2, ... rowcount}, call it "idx";
3) add a column calculated with the following logic if [Added Index] {[idx]} [ID] = [Added Index] {[idx] +1} [ID] then null else [start]