Help on create new column based on 2 other columns

[Alvarom1]

Hi,

I have this table with 2 columns

Invoice	Payment Date
a	01/01/2019
a
a
b	03/08/2019
b
c	04/05/2019
c
c
c
d	07/05/2019
e	04/09/2019
e
e
e
e
f	07/11/2019
f

and I want to generate a column to complete the missing payment dates, that are the same for each invoice.

This is what I should get (thrid column)

Invoice	Payment Date	New Column to get
a	01/01/2019	01/01/2019
a		01/01/2019
a		01/01/2019
b	03/08/2019	03/08/2019
b		03/08/2019
c	04/05/2019	04/05/2019
c		04/05/2019
c		04/05/2019
c		04/05/2019
d	07/05/2019	07/05/2019
e	04/09/2019	04/09/2019
e		04/09/2019
e		04/09/2019
e		04/09/2019
e		04/09/2019
f	07/11/2019	07/11/2019
f		07/11/2019

Can anyone help me with this?

Thanks

Alvaro

[MartynRamsden]

Hi @Alvarom1 

Rather than creating a new column, you can use the Power Query 'Fill' function to fill down the dates in your exisiting column:

•  Open the Power Query Editor and select your query
• Select the Payment Due column
• Navigate to Transform menu then select Fill > Down

Best regards,
Martyn

If I answered your question, please help others by accepting it as a solution.

[Alvarom1]

Thanks Martin,

another issue. Some values on the table are not on the first line of each invoice. Then when I do as you suggest it takes dates from previous invoice. Is there a way to order by two columns? First Invoice, then the value on the second column and after that do the Fill down?

I hope is clear

thanks!

Alvaro

[MartynRamsden · Accepted solution]

Hi @Alvarom1 

Yes, use the arrow next to the column header in the Power Query Editor to set the sort order.

Sort the Invoice column Ascending, then the Payment Date column descending. 

Once that's done, apply the Fill Down on the Payment Date column.

Best regards,
Martyn

If I answered your question, please help others by accepting it as a solution.

[Alvarom1]

Great. it works

Thanks a lot Marty

Good day

Alvaro