Forum Discussion
Solved
Group By - Return latest value only
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 1...
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
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