Delete rows in Pbi using two criteria

[Anonymous]

Hello community, I have the following problem in pbi:

From this table in power bi, I need to delete row records according to FECHA PAGO column and INDICE column, I need to leave a single row for each DATE that contains the largest INDEX value:

ORIGIN TABLE

The table should look like this:

[v-xinruzhu-msft · Accepted solution]

Hi @Anonymous 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sxLL0otzlfSUXLOLyoBUob6FvpGBkbGQKaRoaWFUqwOQUXmxCgyI0aRMboi52Agaa5vbIhQZIzhJmyKIG4KSEzHqcIMWYVvYhGmClOCKkyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Movim = _t, Conce = _t, Date = _t, Indi = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Movim", type text}, {"Date", type date}, {"Indi", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", each if [Indi]=List.Max(Table.SelectRows(#"Changed Type",(x)=> x[Date]=[Date])[Indi]) then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

Best Regards!

Yolo Zhu

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

[slorin]

Hi,

= Table.FromRecords(Table.Group(Your_Source, {"DATE"}, {{"Data", each Table.Max(_,{"INDEX"}), type record}})[Data])

Stéphane