Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Gladiator909
Helper I
Helper I

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:

 

MakeDateTimePrice
Acura01/02/202310:05 AM5000
Acura01/02/202311:006000
Acura03/03/202313:002000
BMW05/06/202314:001000
BMW06/06/202317:00200
BMW06/06/202316:005000

What I would like the result to look like:

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

 

Basically:

 

MakeMax DateLatest TimeLatest Price
Acura03/03/202313:002000
BMW06/06/202317:00200

 

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

Gladiator909_0-1679066156329.png

 

This is the grouping that I have done:

Gladiator909_1-1679066186652.png

 

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:

 

Gladiator909_2-1679066266457.png

 

any help would be greatly appreciated

 

Thank you

 

 

 

 

1 ACCEPTED SOLUTION
2 REPLIES 2
ChrisMendoza
Resident Rockstar
Resident Rockstar

@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 

image.png






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.