How to get the find the recent records from the duplicate records using Power Query Editor

[susheeltyagi]

HI,

I need small help. I am importing the data from SQL Server database. I would like to get the most recent record on the basese of  Serial Number and UserTime using Power Query at the stage of Transformation of data.

How to do it ?

Input Data as below :

(Table : Device)

Expected output after Transform of data using Power Editor :

Thanks in advance

Susheel

[jennratten]

Hello - this is how you can do it.

BEFORE

AFTER

SCRIPT

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc5bCsQwCAXQrRS/2+FezcO4ldD9b6MGhs7HFGYgSFSOOqdQdhkoQG09v15HRrUVwHHAD+pGDSDfC4Cc+xT9rbjRAh5st6p/7UrVg36rln1jZm2UNaD7KhT/YmbBz4n9mdUnpm92Xg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, SerialNumber = _t, RemaingItems = _t, ReadingRemaining = _t, UserTimer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"SerialNumber", Int64.Type}, {"RemaingItems", Int64.Type}, {"ReadingRemaining", Int64.Type}, {"UserTimer", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SerialNumber"}, {{"MaxTimer", each List.Max([UserTimer]), type nullable datetime}, {"Data", each _, type table [Id=nullable number, SerialNumber=nullable number, RemaingItems=nullable number, ReadingRemaining=nullable number, UserTimer=nullable datetime]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "MaxValue", each Table.SelectRows ( [Data], (x) => x[UserTimer] = [MaxTimer] )),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"MaxValue"}),
    #"Expanded MaxValue" = Table.ExpandTableColumn(#"Removed Other Columns", "MaxValue", {"Id", "SerialNumber", "RemaingItems", "ReadingRemaining", "UserTimer"}, {"Id", "SerialNumber", "RemaingItems", "ReadingRemaining", "UserTimer"})
in
    #"Expanded MaxValue"