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,

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"

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        This worked great! Thank you. 

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

      • hmenco's avatar
        hmenco
        Frequent Visitor
        5 years ago

        I need an additional custom column PQ Editor that COUNTS the number of OrderNumber entries..

         

         

        Goal is to remove the duplicates and get the latest entry based on RunDate.

         

         

         

        I have tried the Table.Buffer but it seems not to be applicable because it slows down the performance/process. I thought of this workaround instead...