Replace Value based on another column in previous row

[2Old4This]

I would very much appreciate help as I have tried many different methods to replace text in each row of a column or leave blank based on another field. When that other field is repeated then this field will be made blank with no text.

The Replace Value has five arguments and using the third part of the argument, which is the value of the text to replace with, I can successfully do an Add Column, but the same formula is not working for replace text. I will paste these below.

This below works to add a column -

= Table.AddColumn(#"Replaced Value1", "Custom", each try (if #"Replaced Value1"[Job Number]{[Index] - 1} = [Job Number] then "" else [Work Description]) otherwise [Work Description])

Result which is correct (note that the last two records in the field are for different job numbers although they are the same trext and that is why they have correctly not changed) -

Custom

Travel to Corryong, diagnose problem with heat pump not heating water, and apply fix on 02/02/2023

31/01/2023 - Attend diagnose order part 03/02/2023 - Replace temperature sensor, set clock and temperature on panel, test and report

Install 215 ltr heat pump hws on 15/02/2023

Install 215 ltr heat pump hws on 15/02/2023

But this replace text, below does not throw any visible fault, and the table remains unchanged.  It does not work to replace text using the same code which worked for add column -

= Table.ReplaceValue(#"Reordered Columns1", "Work Description", each try (if #"Reordered Columns1"[Job Number]{[Index] - 1} = [Job Number] then "" else [Work Description])otherwise [Work Description],
Replacer.ReplaceText,{"Work Description"})

Result which does not replace values -

Work Description

Travel to Corryong, diagnose problem with heat pump not heating water, and apply fix on 02/02/2023

Travel to Corryong, diagnose problem with heat pump not heating water, and apply fix on 02/02/2023

31/01/2023 - Attend diagnose order part 03/02/2023 - Replace temperature sensor, set clock and temperature on panel, test and report

31/01/2023 - Attend diagnose order part 03/02/2023 - Replace temperature sensor, set clock and temperature on panel, test and report

Install 215 ltr heat pump hws on 15/02/2023

Install 215 ltr heat pump hws on 15/02/2023

Please help!!! and thanks in advance.

[adudani]

hi @2Old4This ,

= Table.AddColumn(#"Replaced Value1", "Custom", each try (if #"Replaced Value1"[Job Number]{[Index] - 1} = [Job Number] then "" else [Work Description]) otherwise [Work Description])

Try trimming, Cleaning and I suggest putting it in UPPER  as well.

This might resolve the issue.
Seems like the comparison to previous row and replacing with  "" works..

[2Old4This]

Thank you for your reply but...

No this does not help me Adudani. As I stated in my question the code you pasted above actually does work. I can add a column and it is correct using this formula. What I cannot do is use that same formula for the new part of table.replacevalue which is what I want to do. As below-

= Table.ReplaceValue(#"Reordered Columns1", "Work Description", each try (if #"Reordered Columns1"[Job Number]{[Index] - 1} = [Job Number] then "" else [Work Description])otherwise [Work Description],
Replacer.ReplaceText,{"Work Description"})

Also I do not understand your trim and clean?

or replace with UPPER. ? I am assuming you mean uppercase, but which part? or all?