Forum Discussion
Duplicate data updating
nixonyx Here's easy way to do it.
1. I created samle dataset to mock your scenario. Record 3 and 4 are duplicates based on date.
2. Load data into PowerBI desktop -> Edit Queries -> Select RecordNum column -> Transform ribbon -> Pivot Column
3. In Pivot Column dialog box -> select Latestdate field (in your dataset this will be your date column) in 'Values Column' option. Under 'Advanced Option' select Maximum. OK
4. This will give you unique record number with maximum date.
5. Next select all four transformed columns (in this example it resulted in four columns due to 4 record number) and click Unpivot Columns.
This will give you result you're after.
Thank you for the suggestion (sorry for the delay in replying). My full data set is a little more complicated so will try a cut down version first to test. It looks sensible so I have high hopes for it doing what I need.
Thanks!
edit - note that I'm typically dealing with only a few thousand records at a time!
-vpray
This may not be the most efficient way (still learning), but I have to do this type of thing all the time with inspections and re-inspections. I just use a duplicate of the first query, group it by an appropriate ID column, adding a max column on the date field while I'm at it. Then I merge this helper query back into the main query using the ID and a one-to-one join, so only the "max-date records" are shown. Hope that makes sense, it's easier than it looks in print!
