Solved Covid-19 - Recognize hamster Purchases with Power Query

pinarello

Member
Joined
Jun 21, 2019
Messages
214
Reaction score
4
Points
18
Location
Germany
Excel Version(s)
Office 365
Hello,

since just now, I asked myself the question whether Power Query can be used to identify hamster purchases from a list of customer orders.

After some considerations I came to the conclusion that it should work and then started to create an evaluation based on dummy data.

For the detection of hamster purchases I use historical order data and determine the average order quantities from this data. The current order quantity is not considered. If the current order quantity is higher than the average order quantity * hamster factor in %, then this is a hamster purchase.

As a result, I have created a parameter-controlled evaluation in which the following specifications can be made:

1. number of days to determine the period (order date - days) to be taken into account for determining the average order quantities.
The current order quantity is not taken into account when determining the average order quantity.

2. hamster from % to determine from which positive average excess a hamster purchase is present.

3. hamster of the last n days. Only if a hamster purchase is in the period today minus the specified days until today, the recognized hamster purchase is also marked in the list.

Basically, the query could also be extended, for example, to determine the average order quantities of an article of all customers. These average quantities could then be used to identify hamster purchases from new customers or from customers who have not ordered for a long time.

To reduce the effort involved in entering data, I have created an index to simulate the order numbers for this demo folder.

In the final version it would be sufficient to show only those order items that were recognized as hamster purchases. But to make the results easier to understand, all data is always shown.

Note: After changing the control parameters the query must be refreshed!

Also I can state that the realisation of this evaluation was much easier in the end than I thought at the beginning.

Have fun with the evaluation and maybe there are still some good suggestions.

View attachment Excel PQ - Covid-19 - Recognize hamster purchases.xlsx
 
Last edited:
Back
Top