Drop duplicate rows retaining latest date

[parhamasq]

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.

[v-yuta-msft · Accepted solution]

@parhamasq ,

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.

[jamesrwrc]

@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: 

[az38]

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

[parhamasq]

This is an error:

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

[az38]

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

[parhamasq]

Thank you

I'm a new joiner in Power BI,

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