Forum Discussion
Duplicate data updating
I have a list of service desk incident records. There are some duplicates due to records being reopened. That is a process issue that is not going away any time soon.
Currently, when reporting, we identify the duplicates, and find the latest close date (max value). All duplicate records are removed and the max value is applied to the close date of the one remaining record.
I have an excel macro to do this as part of a wider macro to transform this and other source data into the final report. We are trialling PowerBI as a more efficient way of achieving this.
How would I perform this action in PBI? I am struggling to find good code examples for the advanced query editor so don't understand the syntax being used.
Thanks for any/all suggestions.
7 Replies
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!
I'll have to sit down with this and see what I can come up with but in the mean time, here is the Power Query "M" reference:
https://msdn.microsoft.com/en-us/library/mt211003.aspx
I am having a read through that, good to know I am looking in the right place.
Having spent years learning how to bend excel to my will, this is proving a steep learning curve :)
I'm thinking something along the lines of Table.Distinct with the optional equation criteria:
https://msdn.microsoft.com/en-us/library/mt260775.aspx
