Create a column that takes a value from another row when a criteria is met

[Anonymous]

Hi,

I have a dataset that contains a unique identifier that is on multiple rows.  Each row has 2 possible dates and ab indicator.  I would liek to create a column that returns one date from those available.

ID	Policy or Quote	Quote Date	Policy Date	New Date Column
1	Quote	01/01/2019	null	01/01/2019
1	Policy	null	02/01/2019	01/01/2019
2	Policy	null	03/01/2019	03/01/2019
3	Policy	null	04/01/2019	04/01/2019
4	Policy	null	05/01/2019	04/01/2019
4	Quote	04/01/2019	null	04/01/2019

From the above table you can see the new column I'm trying to create.  I want to show the quote date for an ID which will take the quote date if there is a quaote, but when there is no quote and only a policy, i will take the policy date as the quote date.

I hope this makes sense, and thanks in advance.

[Nathaniel_C]

Hi @Anonymous ,

In Power Query add a conditional column. Use If (table[Quote Date] <> "NULL", table[Quote Date], table[Policy Date])
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Nathaniel_C]

Hi @Anonymous ,

So, looking at your table more closely, the logic is:

Use the quote date for both lines, else use the policy date.

Let me know if that is the case. I will be back on line in a couple of hours.

Nathaniel

[Nathaniel_C · Accepted solution]

Hi @Anonymous 

This checks for a quote date, and if there is not one it uses the policy date and if there is one, then it uses the quote for all rows that have that policy ID. Here is my PBIX POLICY DATE 

Matches your column.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

NewQuoteDate =
IF (
    ISBLANK (
        CALCULATE ( MIN ( PolDate[Quote Date] ), ALLEXCEPT ( PolDate, PolDate[ID] ) )
    ),
    PolDate[Policy Date],
    CALCULATE ( MIN ( PolDate[Quote Date] ), ALLEXCEPT ( PolDate, PolDate[ID] ) )
)

[Anonymous]

Thanks very much for the quick reply.  Is it possibel to only use the policy date when there is no associated quote? Like the instances of ID's 2 & 3?