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 advan...
  • 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,

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"

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    This worked great! Thank you.