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,

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

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    Does this M Query work faster?  If not, then let me know.  I will share a calculated column formula in DAX with you.

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrderNumber", Int64.Type}, {"RunDate", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"OrderNumber", Order.Ascending}, {"RunDate", Order.Ascending}}),
    Partition = Table.Group(#"Sorted Rows", {"OrderNumber"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"RunDate", "Index"}, {"RunDate", "Index"})
in
    #"Expanded Partition"

     

    • hmenco's avatar
      hmenco
      Frequent Visitor
      5 years ago

      Hi Ashish_Mathur, thank you but do you have another work around? The M query takes time to process big data. 

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        I have a DAX calculated column alternative (not an M query alternative).  Are you interested in that?

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