Forum Discussion

talitanieps's avatar
talitanieps
Icon for Advocate I rankAdvocate I
3 years ago
Solved

How to filter values based on the most recent row

Hello PBI Community! 🙂

I'm working with a database that has social media posts information... each row is a unique post, with an unique id, timestamp and unique content; however, when this post is edited, a new row is generated, with a new time stamp, new post id and of course, the content is "new", no matter the modification made. 
I'm struggling to clean this kind of data, because Power Query is a bit limited regarding "duplicated" rows, I can't filter it based on the time, and even if I could, I couldn't find a way to use previous row as an argument for comparison, to have this removed.

So what I have is this:

 

But I want, essentially, is to keep only the most recent record, ignoring the editions:

 

I've created a dummy for this: 
https://drive.google.com/file/d/1wtISXEAbR7WAC_AjgGlUpaOCBqTSAkvl/view?usp=sharing

Any idea is much appreciated!
Thanks in advance! 🙂

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi talitanieps ,

     

    Try:

    = Table.SelectRows(PreviousStepName, each ([date] = List.Max(PreviousStepName[date])))

    all steps:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjBU0lEyNNA3MNI3MjAyVjA0tDI1sDI0AIq6ZRYVl5SlFhVn5ucpxeoA1RphU2sMV6uAotgYm2ITU7jiMIhiBYhqE0zVhlZgM1BVKyrFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, date = _t, post = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"date", type datetime}, {"post", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([date] = List.Max(#"Changed Type"[date])))
in
    #"Filtered Rows"

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi talitanieps ,

     

    Try:

    = Table.SelectRows(PreviousStepName, each ([date] = List.Max(PreviousStepName[date])))

    all steps:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjBU0lEyNNA3MNI3MjAyVjA0tDI1sDI0AIq6ZRYVl5SlFhVn5ucpxeoA1RphU2sMV6uAotgYm2ITU7jiMIhiBYhqE0zVhlZgM1BVKyrFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, date = _t, post = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"date", type datetime}, {"post", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([date] = List.Max(#"Changed Type"[date])))
in
    #"Filtered Rows"

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • talitanieps's avatar
      talitanieps
      Icon for Advocate I rankAdvocate I
      3 years ago

      Hello Gao,

       

      Thanks for the quick response!
      First I tried on the dummy and it worked perfectly, and after that, on my original report.
      It turns out it also worked well, but I found it curious that, on the filter step, it's returning a single row, but on the subsequent step, it's showing all the rows back again, with no more duplicates. 


      Excellent, worked as a charm! Thank you very much, sir! 🙂