Power Query: Last instance a customer purchased a Product

gazmoz17

Board Regular
Joined
Sep 18, 2020
Messages
158
Office Version
  1. 365
Platform
  1. Windows
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
1684761973146.png
 

Attachments

  • Group by.jpg
    Group by.jpg
    139.5 KB · Views: 19

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.
 
Upvote 0
Solution
Thats great thanks Alan, I've managed to pull the correct results, much appreciated 👍
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top