Removing a duplicate row in a primary key table

Power Query
1

I have a dimension table with unique data but ever so often it does come up with duplicates when changes are made automatically and I want a way in power query to automatically only include the most up to date unique record.

Here is an example below for the duplicate record with employee 381

image
image710×728 20.7 KB

How do I automatically keep the record with null values in term date in power query so I can have a unique employee 381.

Your help will be appreciated and I have attached a file for your perusual.

Keep a record in duplicates scenario.xlsx (24.0 KB)

Thanks

1 Like
2

Hi @ambepat,

Based on your description, I added a Group By step and selecting the blank TermDate row when there is more than 1 row in the Table.

image
image850×622 32.4 KB

Here’s your sample file.
Keep a record in duplicates scenario.pbix (35.9 KB)
I hope this is helpful

3

Hi @Melissa thanks for this solution. Could you please expand on the _ here?
image I have been going through your course on power query and it is really good but need to go it through a few times for it to stick in. Lots of little details to pay attention to. The solution works fine.

P

1 Like
4

Hi @ambepat,

Glad to hear you’re enjoying the course and thank you for the positive feedback!

This module covers the “underscore”.

.The each keyword is syntax sugar for a unary function (a function that takes a single argument which is represented by the ‘underscore’). This code is equivalent but maybe harder to read.

image
image857×611 26.6 KB

I hope this is helpful.

1 Like
5

Got it. Will check out the video again. Thanks.

1 Like
6

@Melissa lets say the start dates were unique and you have different start dates for the same employee how do you modify the code to only pick the row where the start date is max?

7

Hi @ambepat,

I’d be happy to look into that with you but please create a new topic, supply your data and work in progress PBIX file.

Thanks!

8

OK Melissa will do. Just thought it will be easier to just continue with this thread as it relates to what you looked at previously but if you want me to create a new one will do.