Power Query: Last instance a customer purchased a Product

[gazmoz17]

Hi,

For a small sample of Product & Inv data I've managed to pull through the last time a Product was sold. However, how do I adapt this to pull through the last time Product was sold to certain customer.

So that my 1st col is "Customer" then "SKU" then "Description" finally "Date"...Date being last instance of sale to said customer.

I think I need to adapt this step somehow (attach).

In excel I think I could do this with Index match. So I'm looking up 23 variables and then only latest instance.

Currently the query outputs via prod per below. I've just deleted the step deleting MaxDate so can check it was working.

Many thanks
Gareth

 

[alansidman]

I don't believe you need the All Rows. Instead, to get the description, add it to your group by. It should be unique to the SKU. If you need more data from the file, then you could do a join back on to itself.

 

[gazmoz17]

*2 variables not 23

 

[gazmoz17]

Thats great thanks Alan, I've managed to pull the correct results, much appreciated