Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SteBecke
New Member

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

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: 

 

IDModification DateHas MachineHas Tooling
119/06/2024YesYes
219/06/2024NoYes
319/06/2024NoYes
419/06/2024NoYes
218/05/2024YesYes
118/05/2024NoYes
518/05/2024YesNo
317/04/2024YesNo

 

The result should look like this:

 

IDModification DateHas MachineHas Tooling
119/06/2024YesYes
219/06/2024NoYes
319/06/2024NoYes
419/06/2024NoYes
518/05/2024YesYes

 

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


Thank you upfront. 

 

Best regards, 

 

Steven 

1 ACCEPTED SOLUTION
audreygerred
Super User
Super User

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

2 REPLIES 2
audreygerred
Super User
Super User

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors