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

[drogzy]

Hi all, 

Here is what I have and I am trying to add the bolded column as a custom column:

ID	Start	End	Rig	A Job
12345	2022-02-10 8:00 AM	2022-02-13 10:00 AM	Delta 10	null
12345	2022-02-12 8:00 AM	2022-2-16 10:00 AM	Delta 6	2022-02-12 8:00 AM

As you can see, I want the A Job column to return the max value of the start column. Now I do not want to group them because it will break my direct query to sql. Looking for help in how to use power query to write the correct formula for the column. 

Thank you 

[BA_Pete · Accepted solution]

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

[Anonymous · Accepted solution]

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]

[BA_Pete · Accepted solution]

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