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
Solved! Go to 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.
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:
Best regards,
Martyn
If I answered your question, please help others by accepting it as a solution.
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
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.
Great. it works
Thanks a lot Marty
Good day
Alvaro
User | Count |
---|---|
116 | |
62 | |
59 | |
48 | |
39 |
User | Count |
---|---|
111 | |
65 | |
63 | |
51 | |
48 |