Forum Discussion

parhamasq's avatar
parhamasq
New Member
6 years ago
Solved

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.
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    6 years ago

    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.

az38's avatar
az38
Community Champion
6 years ago

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's avatar
    parhamasq
    New Member
    6 years ago

    Thank you

    I'm a new joiner in Power BI,

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

     

  • parhamasq's avatar
    parhamasq
    New Member
    6 years ago

    This is an error:

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

    • az38's avatar
      az38
      Community Champion
      6 years ago

      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