If logic = true, then use previous row's value, otherwise another column

Question

I'm a beginner to Power Query and it seems that some of the syntax has changed so have been trying to look this one up for a while and still no further forward. I need to reference the previous row of data, but it needs to be the same column, not a new one.

I'm pulling out employee data and the employee number is only indicated once every 100 lines or so. I have a column to pull the data out and it displays the employee number. I need to repeat that employee number for every line until there is a new employee number. I've created an index field. The previous step name is Employee and the column it creates is [Employee]. Adding a new custom column called [EmpNo] and adding the following just results in errors against every field but the ones matching the [Employee] column indicating it doesn't like #"Employee"{[Index]-1}[EmpNo]

if [Employee] = "" then #"Employee"{[Index]-1}[EmpNo] else [Employee]

Anyone any ideas what I'm doing wrong? If left cell = "" then use above cell else use left cell.

horseyride horseyride · Accepted Answer · 2020-08-20T16:17:22

Looks like you should just be right-clicking the [EmpNo] column and using fill ... down...

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filled Down" = Table.FillDown(Source,{"EmpNo"})
in #"Filled Down"

The code you are trying would NOT WORK for your problem, since the second blank cell would just pull from the first blank cell above that one. For reference, that bad code would be:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [EmpNo]= null then #"Added Index"{[Index]-1}[EmpNo] else [EmpNo])
in #"Added Custom"

If logic = true, then use previous row's value, otherwise another column

1 Answers