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 II
Super User II

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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.