Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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
@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
Proud to be a 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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
103 | |
99 | |
98 | |
38 | |
37 |
User | Count |
---|---|
151 | |
121 | |
73 | |
71 | |
63 |