Latest Price Paid Using Power Query

[matratus28]

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. 

[AlienSx · Accepted solution]

Table.Group(
        Source, {"Product Code"}, 
        {{"Date", each List.Max([Date])}, 
        {"Price Paid", each Table.Sort(_, {"Date", Order.Descending})[Price Paid]{0}}})

[AlienSx · Accepted solution]

Table.Group(
        Source, {"Product Code"}, 
        {{"Date", each List.Max([Date])}, 
        {"Price Paid", each Table.Sort(_, {"Date", Order.Descending})[Price Paid]{0}}})

[matratus28 · Accepted solution]

Thank you  - I'll have a play around with that on a real data set.

[jennratten]

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"

[matratus28]

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 🙂