Inheriting and populate data based on a column

[Anonymous]

Hello!

Im having difficulties with inheriting "Startdate" based on "Orderid".

For my issue - 

Let's take a random ORDERID as in the screenshot (GPA.3315).

The first purchase of the user has its start date and month and called "Purchase" (Yellow square in the screenshot).

The next purchase for the same ORDERID will be "Renewal" as in the screenshot (Red square in the screenshot).

I'm trying to inherit the startdate and startmonth from "Purchase" to all related "Renewal".

This way I can map the different renewals to the original start date of the user.

Any quick solution I can use?

Thanks in advance,

Bar

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

You could use Fill feature.

https://docs.microsoft.com/en-us/power-query/fill-values-column 

Step1:

Sort sort the table by [Orderid]

Step2:

Selecte [Start date] and use fill down feature.

Best Regards,

Jay

[Anonymous]

Anyhelp?

@amitchandak 

[amitchandak]

@Anonymous , Not very clear.

Try a new column like

new column =
var _rnk = rankx(filter(Table, [user]=earlier([user]) && [startmonth]=earlier([startmonth])),[start date])
return
if(_rnk =1, "Purchase", "Renewal")

if needed remove startmonth filter

[Anonymous]

Thanks for the fast replay!

Sorry for not being clean. Im trying to copy the startdate and startmonth from "Purchase" row to the renewals of the same "OrderID".

this is what I'm trying to achieve 

I tried your solution in a custom column but I'm getting an error, what did I do wrong?