Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Alvarom1
Helper I
Helper I

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

1 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. 

 

Capture.JPG

 

 

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.

View solution in original post

4 REPLIES 4
MartynRamsden
Solution Sage
Solution Sage

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

Capture.JPG

 

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. 

 

Capture.JPG

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.