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...
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.

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?

      • ronrsnfld's avatar
        ronrsnfld
        Super User
        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.