Conditional Column

[Oana]

Hello PowerBI power users: 

I hope you can help me find a solution to this problem: I am trying to add a conditional column that will find out an Invoice Due Date if the values in two other columns meet these two criteria: the Vendor Invoice Number is the identical, and the Trans Code is "P". 

The same Vendor Invoice Number can have several rows in the table, and whenever the Trans Code column contains a P there will be an Invoice Due Date, and it will be the same, but when the Trans Code will be a "T" or an "F" that Invoice Due Date column will be blank. I need that column to contain the same date, as an invoice will always have the same Invoice Due Date. 

I dont' want to use Group By in Power Query, because the table has so many more columns, I need the date to be added to where it is missing. I hope someone has done this before and knows a solution, thank you!

Vendor NumberVendor Invoice NumberInvoice DateLine NumberTrans CodeInvoice AmountInvoice Due DateDays Past Due

2472	4106597097	11/19/2021	6	P	$1.00	12/24/2021	14
2472	4106597097	11/19/2021	0	F	$2.00
2567	4106614190	11/30/2021	1	P	$3.00	1/4/2022	3
2567	4106614190	11/30/2021	0	F	$4.00
12345	4106614191	11/30/2021	1	P	$5.00	1/4/2022	3
12345	4106614191	11/30/2021	0	F	$5.00
68945	4106650830	12/20/2021	1	P	$5.00	1/24/2022	-17
68945	4106650830	12/20/2021	2	P	$10.00	1/24/2022	-17
68945	4106650830	12/20/2021	6	P	$5.00	1/24/2022	-17
68945	4106650830	12/20/2021	4	P	$5.00	1/24/2022	-17
68945	4106650830	12/20/2021	0	T	$5.00

[v-shex-msft]

Hi @Oana,

I think the 'fill down' feature in query edit should help with your requirement, you can use them to fill the records which blank with the previous row value.

How to Fill Down Blank or Null Cells in Power Query - Excel Campus
BTW, I also think you need to use the 'group by' function at first and nested the 'fill down' function to prevent the fill function work on the wrong row groups.

Regards,

Xiaoxin Sheng

[MFelix · Accepted solution]

Hi @Oana ,

Taking into account that you want all the same value in the column have you tried sorting the table by Vendor, Vendor invoice number and then by Due Date after that on the due date just do a fill down?

All the blanks will get replaced by the value that is above and all is solved.

Be aware I'm assuming that you have at least one due date filled in your invoices so all of them have the P and not only the T or F.

If that does not happen you need to make a group by and then reference back within the query code.