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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
amitchandak
Super User
Super User

@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...

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

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!



amitchandak
Super User
Super User

@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...

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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