Find latest entry with power query editor

[nrowey]

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.    

[v-xinruzhu-msft]

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]

I will try this next time around Thanks

[nrowey]

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]

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]

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 · Accepted solution]

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]

I did not reliize I could rename to exsisting column names..  This worked Thanks so much...

[ronrsnfld]

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