Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I'm trying to find a solution for finding the latest price paid for a product from a set of sales data using Power Query.
I've seen a few examples of how to achieve it using DAX but can't see if it's possible in Power Query.
My current solution is to just sort my data by date in Excel - newest to oldest - and then vlookup using the Product Code and Price Paid - the vlookup will then just return the 1st and latest price.
A simplified extract of my data is below.
Date | Product Code | Price Paid |
11/09/2023 | AAA123 | 10.99 |
01/01/2023 | AAA123 | 10.49 |
04/02/2023 | AAA123 | 9.65 |
11/09/2023 | BBB234 | 6.99 |
01/01/2023 | BBB234 | 6.23 |
04/02/2023 | BBB234 | 5.99 |
11/09/2023 | CCC456 | 1.15 |
01/01/2023 | CCC456 | 0.95 |
04/02/2023 | CCC456 | 1.22 |
The need to find the latest price is just 1 step in using Power Query to clean my data set, so I can use it in a separate process.
I could use my current method or load into Power Pivot and use DAX, but was trying to use Power Query to run through all the cleaning steps in one process.
Any advice or links to other solutions would be greatly appreciated.
Solved! Go to Solution.
Table.Group(
Source, {"Product Code"},
{{"Date", each List.Max([Date])},
{"Price Paid", each Table.Sort(_, {"Date", Order.Descending})[Price Paid]{0}}})
Table.Group(
Source, {"Product Code"},
{{"Date", each List.Max([Date])},
{"Price Paid", each Table.Sort(_, {"Date", Order.Descending})[Price Paid]{0}}})
Thank you - I'll have a play around with that on a real data set.
Hello! You can group the rows by the product code, get the max date for the product code and then retrieve the price paid for the max date. Here is an example:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc9BDgARDEDRu3QttNVKLHEMcf9rjJEZIezavPSHWoHIYXSM7MFASonGQGhjhGYqYHe6uXwuDvnwaIMO3vI5Z/bSh3CtL9z3Iz5Z/+stXkoRDe/bLOkZn9x/pmd8uWaG1h4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Product Code" = _t, #"Price Paid" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product Code", type text}, {"Price Paid", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Product Code"}, {{"MaxDate", each List.Max([Date]), type nullable date}, {"Data", each _, type table [Date=nullable date, Product Code=nullable text, Price Paid=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Latest Price", each Table.SelectRows ( [Data], (r) => [MaxDate] = r[Date] )[Price Paid]{0}, type number )
in
#"Added Custom"
If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.
Proud to be a Microsoft Fabric Super User
Thank you - it'll be a couple of days before I get chance to play around with that on a real data set, but I'll let you know how I get on - I'm sure it's work fine 🙂
Check out the July 2025 Power BI update to learn about new features.