Forum Discussion

nrowey's avatar
nrowey
Helper I
2 years ago
Solved

Find latest entry with power query editor

I have a dataset, It has a colume name "Dealno" (Text) and another "Rowlastupdated" (DateTime).

The "Dealno" row is repeted everythime someone makes a change and it is timestamped in "Rowlastupdated"

I am trying to filter out thru Power Query to only show the latest "Dealno" row from the "Rowelastupdated" column.

can this be done in power query to filter the entire table.

Thanks for any help.    

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    Hard to tell what you are doing wrong. Even from the UI, the column names don't change for the simple example of two columns -- one with the dealno and one with the date.  When you enter the name of your max date column, it can be whatever you want (even the same as the original date column).

     

    If you are expanding a subtable, you may have to edit that step in the Advanced Editor to get rid of the "renaming" which the UI generated step wants to do. That is merely a matter of deleting the optional newcolumnnames argument.

8 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

    Group by "Dealno" and aggregate by "Max" of "Rowlastupdated"

  • nrowey's avatar
    nrowey
    Helper I
    2 years ago

    Is there any other way besides a Group By , I already have all the visuals built.

    Below is a measure that works but I am trying to get the results in power query........

     

    Last Status = var _max = maxx(filter(ALLSELECTED('Query (2)'), 'Query (2)'[dealno] = Max('Query (2)'[dealno])), 'Query (2)'[rowlastupdated])
    return
    CALCULATE(max('Query (2)'[dealno]), filter(('Query (2)') ,  'Query (2)'[rowlastupdated] =_max))
    • ronrsnfld's avatar
      ronrsnfld
      Super User
      2 years ago

      What am I missing?

       

      I don't understand why the method would make a difference. The end result will be the same in PQ.  Please explain.

       

      You could also do this by 

      • Add custom column
        • Filtered to return only those rows where
          • Dealno = the Dealno in that column and
          • Rowlastupdate = the latest date
      • Delete the original columns
      • Expand the custom column which will be a List of Tables all meeting the specifications
      • Delete the duplicate rows

      But that would take much longer to run and the end result would be the same.

       

      Grouping

      #"Grouped Rows" = Table.Group(#"Changed Type", {"Dealno"}, {
        {"MaxRowLastUpdated", each List.Max([Rowlastupdated]), type nullable date}
        })

      Filtering

       #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
        let 
            dn=Table.SelectRows(#"Changed Type", (r)=>r[Dealno]=[Dealno]),
            maxDate=Table.SelectRows(dn, each [Rowlastupdated]=List.Max(dn[Rowlastupdated]))
        in 
            maxDate, 
            type table[Dealno=text, Rowlastupdated=date]),

       

       

      • nrowey's avatar
        nrowey
        Helper I
        2 years ago

        The reason I dont want to set up a group-by is all the Visuals are completed. When ever I set up a Group-by the column names all change and I have to start over building visuals etc.?

        Or maybe I am doing somthing wrong?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi nrowey 

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

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIyMDLWN9Q3UorVQREwRRUwQxcw1zcECyTBBEzRBcxAArEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"MaxDate", each List.Max([Column2]), type nullable date}, {"Data", each _, type table [Column1=nullable text, Column2=nullable date]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Column2"}, {"Column2"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Data", "Custom", each if [MaxDate] = [Column2] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"MaxDate", "Custom"})
in
    #"Removed Columns"

    It will not change the column name, you can also see it in the attachments.

     

    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.

    • nrowey's avatar
      nrowey
      Helper I
      2 years ago

      I will try this next time around Thanks