Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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"
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 🙂