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,

hmenco's avatar
hmenco
Frequent Visitor
5 years ago

Trying to figure out things.. What if there are other columns to be displayed? The M query worked as well.. But maybe i'll use it on the smaller data. 
How can we display other columns that were hidden in the Partition?

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

I shared an alternative DAX calculated solution yesterday.  Try that one.  

  • hmenco's avatar
    hmenco
    Frequent Visitor
    5 years ago

    I have tried both the M query and DAX calculated column.. Both solutions worked incredibly!

    I will observe M query's performance on BIG data first.. If really not applicable, I'll use your DAX calculated column solution...

     

    Thank you so much for taking time!!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Sure.  Based on whichever works better on your live data, please mark that relevant response as Answer.