Forum Discussion

PowerBI_Query's avatar
PowerBI_Query
Icon for Helper II rankHelper II
1 year ago

Filter duplicate rows with latest date

I have a data table as shown in the sample file   Sheet1 (before)  I need the output as shown in Sheet2 (after) The data is not sorted. I need to exclude duplicate rows using latest date as criteri...
Show and Tell
Tips and Tricks
ronrsnfld's avatar
ronrsnfld
Icon for Super User rankSuper User
1 year ago

Your link doesn't work, but one way is to 

  • Group the table by whatever columns you wish to check for duplications
  • Select the row with the latest date
  • Re-expand the grouped table

 

Table.Group(#"Previous Step",#"List_of_Cols_to_Check_For_Duplicates",{
        {"DeDuped", (t)=>Table.SelectRows(t, each [Date]=List.Max(t[Date]))}

 

The code is placed as an additional step in the Advanced Editor.

 

    • ronrsnfld's avatar
      ronrsnfld
      Icon for Super User rankSuper User
      1 year ago

      Away from my computer now. If you can't figure it out from my answer, I'll check back later.

    • ronrsnfld's avatar
      ronrsnfld
      Icon for Super User rankSuper User
      1 year ago

      That's the same non-functional link you posted earlier.  It returns only: "https’s server IP address could not be found."

  • PowerBI_Query's avatar
    PowerBI_Query
    Icon for Helper II rankHelper II
    1 year ago

    There are several numbers with their respective dates so I can't select a date manually

    • ronrsnfld's avatar
      ronrsnfld
      Icon for Super User rankSuper User
      1 year ago

      Nowhere in my code am I selecting your date manually. You merely need to know the name of the column that contains the dates and the columns that you wish to check for possible duplications. 

      The table group function will select the appropriate date. 

      • The process I listed is merely what the code does.