Forum Discussion
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.
You can achieve this using power query, click query editor and copy the original table. Then click "Transform"-> "Group By" as below:
Then merge the original table with duplicated tabel as below:
The result is as below, you can also refer to the pbix file.
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.
6 Replies
You can achieve this using power query, click query editor and copy the original table. Then click "Transform"-> "Group By" as below:
Then merge the original table with duplicated tabel as below:
The result is as below, you can also refer to the pbix file.
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.
v-yuta-msft, parhamasq, This method will not return the most recent value, but instead the largest, which would give incorrect results if value1 decreases. If 62 is replaced with 42 (for example) in the source table, the final table does not show the most recent update for bill, but instead the largest.
Source:
end result:
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
Thank you
I'm a new joiner in Power BI,Would you please explain to me more about create the table in Power BI?
This is an error:
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
