DAX to Power Query

[youngmasterRD]

Hello all!

Currently, I have this formula in my DAX.

Status = FILTER(ALL(raw),[Timestamp]=CALCULATE(MAX(raw[Timestamp],ALLEXCEPT(raw,raw[ID])))

How can I have a same outcome in Power Query?

Raw table

ID	Timestamp	Status
0001	1st December 2021	New
0001	2nd December 2021	In-Progress
0001	5th December 2021	Completed
0002	2nd December 2021	New
0002	3rd December 2021	In-Progress

Processed table

ID	Timestamp	Status
0001	5th December 2021	Completed
0002	3rd December 2021	In-Progress

Thanks in advance!

[BA_Pete · Accepted solution]

Hi @youngmasterRD ,

You need to group your raw table on [ID] with the All Rows aggregator, then grab the row that features the MAX of [Timestamp].

Example query code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMFTSUTIsLlFwSU1OzU1KLVIwMjACifmllivF6sCVGOWlYCjxzNMNKMpPL0otLkZWalqSgaHUOT+3ICe1JDUFptAIh5lI1oKUGBcRsDYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Timestamp = _t, Status = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Timestamp", type text}, {"Status", type text}}),
    groupId = Table.Group(chgTypes, {"ID"}, {{"data", each _, type table [ID=nullable number, Timestamp=nullable text, Status=nullable text]}}),
    addMaxRecord = Table.AddColumn(groupId, "maxRecord", each Table.Max([data], "Timestamp")),
    expandMaxRecord = Table.ExpandRecordColumn(addMaxRecord, "maxRecord", {"Timestamp", "Status"}, {"Timestamp", "Status"}),
    remOthCols = Table.SelectColumns(expandMaxRecord,{"ID", "Timestamp", "Status"})
in
    remOthCols

Example query output:

Pete