cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
parhamasq
New Member

Drop duplicate rows retaining latest date

Hi All,

 

I'd like to remove duplicates in my dataset based on a logical primary key (ID) and retain the latest modified records values.

Capture.PNG

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

@parhamasq ,

 

You can achieve this using power query, click query editor and copy the original table. Then click "Transform"-> "Group By" as below:

Capture.PNG 

 

Then merge the original table with duplicated tabel as below:

2.PNG 

 

The result is as below, you can also refer to the pbix file.

3.PNG 

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yuta-msft
Community Support
Community Support

@parhamasq ,

 

You can achieve this using power query, click query editor and copy the original table. Then click "Transform"-> "Group By" as below:

Capture.PNG 

 

Then merge the original table with duplicated tabel as below:

2.PNG 

 

The result is as below, you can also refer to the pbix file.

3.PNG 

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

az38
Super User
Super User

Hi @parhamasq 

try to use a new calculated table like this

NewTable = 
ADDCOLUMNS(
SUMMARIZE(
'Table';
'Table'[ID];'Table'[Title];"Last Modified new";MAX('Table'[Last Modified])
);
"Value";calculate(max('Table'[Value1]);filter('Table';'Table'[Last Modified]=[Last Modified new] && 'Table'[ID]=[ID])))

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

This is an error:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

hi @parhamasq 

do you have rows which has a different ID and Title values in your data model?

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Thank you

I'm a new joiner in Power BI,

Would you please explain to me more about create the table in Power BI?

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!