Deleting Duplicate Rows

Hi woofwoof123 ,

First of all, many thanks to ryan_mayu  for your very quick and effective replies.

Based on my testing, please try the following methods as workaround:

1.Create the simple table.

2.Create the index column in power query editor.

3.Create the calculated column to rank.

Rank = 
VAR _ticket = 'Table'[Ticket]
VAR _person = 'Table'[Person]
RETURN
RANKX(FILTER(ALL('Table'),'Table'[Ticket] = _ticket && 'Table'[Person] = _person),[Index],,ASC)

4.Create the calculated column to flag the column.

Flag = 
VAR _ticket = 'Table'[Ticket]
VAR _person = 'Table'[Person]
var _maxbought = MAXX(FILTER(ALL('Table'), 'Table'[Ticket] = _ticket && 'Table'[Person] = _person),[Bought])
var _count = COUNTX(FILTER(ALL('Table'),'Table'[Ticket] = _ticket && 'Table'[Person] = _person),[Ticket])
return
IF(
    _count = 1 && [Rank]=1,
    1,
    IF(
        _count > 1 && 'Table'[Bought] = _maxbought,1,0)
)

5.Create the new calculated table.

Table 2 = 
var _table1 = FILTER('Table',[Flag]=1)
return
SUMMARIZE(
    _table1,[Ticket],[Fund],[Bought],[Person])

6.The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

you can also do that in PQ

let
Source = Table,
Custom1 = Table.AddColumn(Source,"Rank",each Table.RowCount(Table.SelectRows(Source,(x)=>x[Bought]>[Bought] and x[Ticket]=[Ticket] and x[Person]=[Person]))+1),
#"Filtered Rows" = Table.SelectRows(Custom1, each ([Rank] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Rank"})
in
#"Removed Columns"

pls see the attachment below

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY8/D4IwFMS/S2eW/qHOlTJoaGKEjTDU2MiA1Fii4dv7WkKpMXG5N/xy9+7aFglxqlCGpL2BUgIirvqOuiyiQk+g2KO9vaSk7o15wOUsT6GS3mXfoISCHLWz4w/i3iNcP5g5sMK6afNhyuAo/VygLJN3O+5TpX6ZwA7n2B/TtL8qGxH7c+YD5TzoMYWLjzD+B+b8a0RTV1tsGChW34rChpxF1H0A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ticket = _t, Fund = _t, Bought = _t, Person = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket", type text}, {"Fund", type text}, {"Bought", Int64.Type}, {"Person", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Ticket", "Person"}, {{"All", each Table.Max(_,"Bought")}}),
    #"Expanded All" = Table.ExpandRecordColumn(#"Grouped Rows", "All", {"Fund", "Bought"}, {"Fund", "Bought"})
in
    #"Expanded All"

Hope this helps.