Group By - Return latest value only

[Gladiator909]

 HI there,

I am trying to use Power BI Query Editor to return the latest data from the following table:

Make	Date	Time	Price
Acura	01/02/2023	10:05 AM	5000
Acura	01/02/2023	11:00	6000
Acura	03/03/2023	13:00	2000
BMW	05/06/2023	14:00	1000
BMW	06/06/2023	17:00	200
BMW	06/06/2023	16:00	5000

What I would like the result to look like:

- Return the latest date and time for each make with the last sold price

Basically:

Make	Max Date	Latest Time	Latest Price
Acura	03/03/2023	13:00	2000
BMW	06/06/2023	17:00	200

However, what the Power Query is returning is the following:

This is the grouping that I have done:

Now I understand I have used "Max" for the price but I don't seem to figure out what else I can use for "Operation" that would return me the latest price

I tried returning "All Rows" and only select the price but it returned the following:

any help would be greatly appreciated

Thank you

[ChrisMendoza]

@Gladiator909 - You could do as:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc2xCoAwDATQX5HMhV5TG6Gb31E6qLOLIP6+kdIiikKGO+5BUqJx2beJDMFZsGWw1+IQEToNAQBl88Vc1NmQvJS3elX5oriqeT0uEyykmb4Y9zByN0Mx4c9I+0U5nw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [make = _t, date = _t, time = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"make", type text}, {"date", type date}, {"time", type time}, {"Price", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"date", type text}, {"time", type text}}, "en-US"),{"date", "time"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"datetime"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"datetime", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"make"}, {{"Grouped", each _, type table [make=nullable text, datetime=nullable datetime, Price=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Max Column", each Table.Max([Grouped],"datetime")),
    #"Expanded Max Column" = Table.ExpandRecordColumn(#"Added Custom", "Max Column", {"datetime", "Price"}, {"datetime", "Price"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Max Column",{"Grouped"})
in
    #"Removed Columns"

which returns 

[amitchandak · Accepted solution]

@Gladiator909 , refer to the video from Curbal for the power query

https://www.youtube.com/watch?v=QaodJFeX49k

You can refer to the measure way

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...