Power Query Statements for New and Repeated Order (Custom Column)

Question

I have a Sales table consists of Date, Customer and Product columns. I want to segregate the data into two parts, New and Renew order. Each customer can order many types of products and each new product ordered by the customer will be state as New and if the same customer order the same product days or months later, it will be state as Renew.

Here is my sample data and column that I want to create:

So far I created a column by merging Customer and Product into one column to make it unique and got stuck to sort it by date. Or is there any other way that will make this easier? Thank you.

Answer 1

Maybe this helps, but it just indicates if its a New or Renew.

You can group your table by Customer and Product and then count the rows. If the row count is 1 it is a only New product. If the count is bigger than 1 it is a Renew.

Answer 2

I've found a way. Here's what I did. I combined Customer and Product so it became unique and created a calculated column like this:

New/Renew = 
VAR counta =
    CALCULATE (
        COUNTROWS ( 'Sales' ),
        FILTER (
            ALLEXCEPT ( 'Sales', 'Sales'[CustomerXProduct] ),
            'Sales'[Date] < EARLIER ( 'Sales'[Date] )
        )
    )
RETURN
    IF ( counta = 0, "New", "Renew" )

Answer 3

There is another way to get this done with just an excel formula.

A column has to be created which combines the product and customer as such:

=B2&C2

Then the following formula is used in another column that checks the newly created column. Place the formula in the first row of the column and copy it downward:

=IF(COUNTIF($D$2:D2,D2)=1,"New","Renew")

COUNTIF($D$2:D2,D2) counts how many times cell D2 is in the range $D$2:D2. When the formula is copied down the range expands as well, so the range corresponds to all past orders.

Sample Excel File