Forum Discussion

A-kitaev's avatar
A-kitaev
New Member
9 years ago
Solved

Remove duplicates - keep last vs keep first

Hi,

I need to remove rows in a table that have duplicate values and I need to keep last one. Power BI by default keeps first one regardless of sorting order. I tried to use buffered table in advanced editor, this didn't help. Any ideas how to achieve this?

  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    A-kitaev

     

    In this scenario, if you want to keep the last records associated with each group column. You can build a calculated table aggregating values with max sort within each group.  

     

    I assume you a have a table like below:

     

     

    Then you can create a calculated table like below:

     

    Table = SUMMARIZE(
	Table3,Table3[Name],
	"Last Value",
	CALCULATE(SUM(Table3[Value]),
		FILTER(Table3,Table3[Sort]=MAX(Table3[Sort]))
	)
	)

     

     

    Regards,

17 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    9 years ago

    A-kitaev

     

    In this scenario, if you want to keep the last records associated with each group column. You can build a calculated table aggregating values with max sort within each group.  

     

    I assume you a have a table like below:

     

     

    Then you can create a calculated table like below:

     

    Table = SUMMARIZE(
	Table3,Table3[Name],
	"Last Value",
	CALCULATE(SUM(Table3[Value]),
		FILTER(Table3,Table3[Sort]=MAX(Table3[Sort]))
	)
	)

     

     

    Regards,

    • A-kitaev's avatar
      A-kitaev
      New Member
      9 years ago

      Thanks! It's not that elegant as I hoped it would be, but it works

      • RichieRich's avatar
        RichieRich
        Advocate III
        7 years ago

        There's a more elegant method here which I've just used with Table.Buffer. https://www.youtube.com/watch?v=rqDdnNxSgHQ
        The crucial part in the video is after the 4 minute mark.....
        The Table.Buffer command saves the sort prior to removing the duplicates ensuring you get the latest.
        The Table.Buffer has to be added manually in the advanced editor
        Here is an example I did where "Registration" is the group on which I'm removing duplicates and keeping the latest record (First of the date for each of the group, date descending)

        let
        Source = #"IVMS - As Posted View - Unstructured Master Query",
        #"Sorted Rows" = Table.Sort(Source,{{"Calendar Year/Month.Calendar Year/Month Level 01", Order.Descending}}),
        #"Buffer table"= Table.Buffer(#"Sorted Rows"),
        #"Removed Duplicates" = Table.Distinct(#"Buffer table", {"Registration"})
        in
        #"Removed Duplicates"

    • hmenco's avatar
      hmenco
      Frequent Visitor
      5 years ago

      Hi! Can you help me add a custom column counting the number of entries? Outcome shall be the same as the SORT column used here. Thank you!

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        Hi,

        Share some data, describe the question and show the expected result.

  • flex99's avatar
    flex99
    Helper I
    5 years ago

    The trick from RichieRich is a very elegant straight forward approach that provided a simple solution for my even more complex duplicates problem! Thanks a lot!