Group values in one column based on a date in another column.

[SteBecke]

Hello everyone, 

I would need your support on grouping values. 

I have a big table with +/-250 columns. There is one column "ID", where I can have the same value multiple times. 

Now my target is to group the whole table based on a column "Modification Date" (use the last one) to have only the latest entry for "ID". I hope it is understandable. 

Here an example: 

ID	Modification Date	Has Machine	Has Tooling
1	19/06/2024	Yes	Yes
2	19/06/2024	No	Yes
3	19/06/2024	No	Yes
4	19/06/2024	No	Yes
2	18/05/2024	Yes	Yes
1	18/05/2024	No	Yes
5	18/05/2024	Yes	No
3	17/04/2024	Yes	No

The result should look like this:

ID	Modification Date	Has Machine	Has Tooling
1	19/06/2024	Yes	Yes
2	19/06/2024	No	Yes
3	19/06/2024	No	Yes
4	19/06/2024	No	Yes
5	18/05/2024	Yes	Yes

I hope that someone can help me. Maybe it's easy and I do a simple mistake. 

Thank you upfront. 

Best regards, 

Steven 

[audreygerred · Accepted solution]

Hi! In Power Query you can sort the date column in descending order then on the ID column, remove duplicates. This will leave you with only the most recent row of each ID.

I would also suggest moving to a star schema model rather than a denormalized table with so many columns. Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

[SteBecke]

Hi Audrey, 

thank you for the solution.

Regarding the Star Schema Model, I have some doubts.

I will get monthly reports in a new excel sheet. As by now I do append querries to combine them. 

In case I split my big table to many smaller sheets, I don't know how to easy and fast repeat the steps to turn into Star Schema Model this for all the coming months. 

Any Idea? 

Thank you.