Forum Discussion
Creating a custom column to return max value from another column in the same table
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
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
2 Replies
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