Add a conditional column using a conditional test referencing previous record

[desmondmclellan]

In the data below the original source information is reflected in columns called "Date","Start Time" and "End Time"

I add a new column called "Date Cleaned" and in this column I would like to relect the formula in column called "Formlula". In essence I want to add a date in in this column based on a condition thereby ensuring the correct date is added. Easy in excel but I am finding it difficult in Power Query. Any assistance would be greatly appreciated. 

	A	B	C	D	E
4	Date	Date Cleaned	Formula	Start Time	End Time
5	Fri 01-Dec-2023	Fri 01-Dec-2023	if (A5<>0,A5,B4)	7:30AM	9:15AM
6		Fri 01-Dec-2023	if (A6<>0,A6,B5)	9:15AM	10:30AM
7		Fri 01-Dec-2023	if (A7<>0,A7,B6)	10:30AM	11:00AM
8		Fri 01-Dec-2023	if (A7<>0,A8,B7)	11:00AM	12:30PM
9		Fri 01-Dec-2023	if (A7<>0,A9,B8)	12:30PM	1:00PM
10		Fri 01-Dec-2023	if (A7<>0,A10,B9)	1:00PM	2:00PM
11		Fri 01-Dec-2023	if (A7<>0,A11,B10)	4:00PM	5:06PM
12	Mon 04-Dec-2023	Mon 04-Dec-2023	if (A7<>0,A12,B11)	8:00AM	9:15AM
13		Mon 04-Dec-2023	etc	9:15AM	11:00AM
14		Mon 04-Dec-2023		11:00AM	11:30AM
15		Mon 04-Dec-2023		11:30AM	12:30PM
16		Mon 04-Dec-2023		12:30PM	1:30PM
17		Mon 04-Dec-2023		1:30PM	3:36PM

[Vijay_A_Verma]

Easiest way is to insert an Index column and put following formula in a custom column where #"Added Index" is previous step

if [Date] = null or [Date] = "" then try #"Added Index"[Date Cleaned]{[Index]-1} otherwise null else [Date]

[desmondmclellan]

Thanks Vijay.

It did not work because Power Query gave me error in that it said [Date Cleaned] wasnt defined. However it did put me on the path to solving it by adding a new column replicating the values in Date Column and then using Table.ReplaceValue function. Error is no longer there, howeve the logic for Table.Replace Function does not give me the answer I need. It is meant to replace all those that are "null" with the date above it so that all nulls have a date in the field. So help in solving this would be appreciated. See Table and code below

[Vijay_A_Verma]

if you could create an Excel file with some dummy data and upload the file to any file hosing service like Onedrive, Google drive, Box, Dropbox etc and share me the link here, I can give the solution which you are seeking. 

[desmondmclellan]

I have created a file and it is located here with dummy data. Column C is what I want to end up with.

https://1drv.ms/x/s!Av2xPMKBTIxvg_RY-6DYWK-b-lY4hw?e=7Fsd9d 

[Vijay_A_Verma · Accepted solution]

I am giving a very simple solution to you which doesn't require Index.

Create a duplicate column named Date Cleaned, Fill down on this column and Reorder this column

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Content.Date", "Date Cleaned"),
    #"Filled Down" = Table.FillDown(#"Duplicated Column",{"Date Cleaned"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Index", "Content.Date", "Date Cleaned", "Content.Start Time", "Content.End Time", "Content.Time Code"})
in
    #"Reordered Columns"

[desmondmclellan]

Thanks a much easier solution. A function I did not know existed but will use again.