Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Delete rows in Pbi using two criteria

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:

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • slorin's avatar
    slorin
    Super User
    2 years ago

    Hi,

     

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

    Stéphane 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.