Forum Discussion
Power Query Group by Earliest occurrence another date field
I would like to filter my data so i only have the unique quality deadline dates by product ID and the earliest Log Date they first appeared.
So each day we recieve a log of product quality checks, the quality team will sometimes modify the deadline date, however we do not have clear record the dates when quality check deadline has been amended. So within Power Query would like to identify for each unique product ID the unique Quality Check Deadline date and the corresponding earliest log date to match this.
| Product ID | Quality Check Deadline | Log Date |
| 1 | 09/10/2024 | 01/07/2024 |
| 1 | 09/10/2024 | 30/06/2024 |
| 1 | 09/10/2024 | 29/06/2024 |
| 1 | 09/10/2024 | 28/06/2024 |
| 1 | 09/10/2024 | 27/06/2024 |
| 1 | 04/09/2024 | 26/06/2024 |
| 1 | 04/09/2024 | 25/06/2024 |
| 1 | 04/09/2024 | 24/06/2024 |
| 1 | 17/08/2024 | 23/06/2024 |
| 1 | 17/08/2024 | 22/06/2024 |
| 2 | 12/09/2024 | 01/07/2024 |
| 2 | 12/09/2024 | 30/06/2024 |
| 2 | 12/09/2024 | 29/06/2024 |
| 2 | 01/09/2024 | 28/06/2024 |
| 2 | 01/09/2024 | 27/06/2024 |
| 2 | 01/09/2024 | 26/06/2024 |
| 2 | 01/09/2024 | 25/06/2024 |
| 2 | 10/08/2024 | 24/06/2024 |
| 2 | 10/08/2024 | 23/06/2024 |
so based on table above would aim to achieve following
| Product ID | Quality Check Deadline | Deadline Updated |
| 1 | 09/10/2024 | 27/06/2024 |
| 1 | 04/09/2024 | 24/06/2024 |
| 1 | 17/08/2024 | 22/06/2024 |
| 2 | 12/09/2024 | 29/06/2024 |
| 2 | 01/09/2024 | 25/06/2024 |
| 2 | 10/08/2024 | 23/06/2024 |
Hola. Sigues con la agrupacion. Agrupas de modo avanzado. Seleccionas solo la columna Product ID. Solo trae una agregacion, presionas el boton de "Agregar agregación". Para tener las dos columnas Quality Check Deadline y Log Date. En la primera tabla defines minimo y en la segunda tabla defines maximo para las dos columnas a agregar.
Tabla 1.
Tabla 2.
¿Si respondí tu pregunta? ¡Recuerda marca mi publicación como la solución!
4 Replies
Create a Blank Query and use the below M Code and test this out.
let Source = Table, GroupedRows = Table.Group(Source, {"Product ID", "Quality Check Deadline"}, {{"Deadline Updated", each List.Min([Log_Date]), type date}}) in GroupedRowsThank you so much, this has worked perfectly
I now wish to create two duplicates of this table one for the earliest deadline updated date by product ID and include the associated quality check deadline on this date and the other one would be the latest deadline updated date.
So two seperate tables
Product ID Quality Check Deadline Earliest Deadline Updated 1 17/08/2024 22/06/2024 2 10/08/2024 23/06/2024 Product ID Quality Check Deadline Latest Deadline Updated 1 09/10/2024 27/06/2024 2 12/09/2024 29/06/2024 Hola. Sigues con la agrupacion. Agrupas de modo avanzado. Seleccionas solo la columna Product ID. Solo trae una agregacion, presionas el boton de "Agregar agregación". Para tener las dos columnas Quality Check Deadline y Log Date. En la primera tabla defines minimo y en la segunda tabla defines maximo para las dos columnas a agregar.
Tabla 1.
Tabla 2.
¿Si respondí tu pregunta? ¡Recuerda marca mi publicación como la solución!
Hola. Intenta con esta agrupacion. Debes seleccionar las dos columnas Product ID y Quality Check Deadline. Luego clic derecho y seleccionas "agrupar por" y haces los pasos de la imagen. Importante, seleccionar Operacion MIN de la columna Long Date.
¿Si respondí tu pregunta? ¡Recuerda marca mi publicación como la solución!
