Forum Discussion

Alvarom1's avatar
Alvarom1
Icon for Helper I rankHelper I
6 years ago
Solved

Help on create new column based on 2 other columns

Hi,

 

I have this table with 2 columns

InvoicePayment Date
a01/01/2019
a 
a 
b03/08/2019
b 
c04/05/2019
c 
c 
c 
d07/05/2019
e04/09/2019
e 
e 
e 
e 
f07/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)

InvoicePayment DateNew Column to get
a01/01/201901/01/2019
a 01/01/2019
a 01/01/2019
b03/08/201903/08/2019
b 03/08/2019
c04/05/201904/05/2019
c 04/05/2019
c 04/05/2019
c 04/05/2019
d07/05/201907/05/2019
e04/09/201904/09/2019
e 04/09/2019
e 04/09/2019
e 04/09/2019
e 04/09/2019
f07/11/201907/11/2019
f 07/11/2019

 

Can anyone help me with this?

 

Thanks

 

Alvaro

Need Help
  • MartynRamsden's avatar
    MartynRamsden
    6 years ago

    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.

4 Replies

  • MartynRamsden's avatar
    MartynRamsden
    Icon for Solution Sage rankSolution Sage
    6 years ago

    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's avatar
      Alvarom1
      Icon for Helper I rankHelper I
      6 years ago

      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's avatar
        MartynRamsden
        Icon for Solution Sage rankSolution Sage
        6 years ago

        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.