Conditional fill transformation based on preceding value

[cymorg]

How would I transform column 1 such that blank values are replaced with the value from the preceding row, so rows 266 & 267 would contain "Use Of Fire Evacuation Chai", row 270 "2001 Manual Handling (Indoor), 272 "1251 Cscs - Health And Safety At" etc.  

In psudo code it would be something like...

if currentRow.column1.value does not contain data then currentRow.column1.value = precedingRow.column1.value

[v-yuta-msft]

@cymorg ,

So your requirement is to refer to previous or next row in power query, right? You can achieve this without adding an addtional index column, please refer to this blog: https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-po...

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vvelarde]

@cymorg 

Hi, In Query Editor with Fill Down Option.

Regards

Victor

[cymorg]

In this scenario "In Query Editor with Fill Down Option" does nothing.

[Vvelarde · Accepted solution]

@cymorg 

Ok, Please follow this:

1. Select your column

2. Go to Tranform - Replace Values 

Value to Find    --- Empty

Replace with ---- null

3. Transform ---Fill Down

Ready

Regards

Victor

[cymorg]

@Vvelarde Victor - So, Transform --- Fill Down creates a Table.FillDown M Query function call.  This function acts on the specified  column's null values, ignoring non-null values.  Therefore, converting empty strings to null before calling this function results in the desired outcome.

It's perfectly clear now thanks to you. 10/10 Thanks.