Forum Discussion

2Old4This's avatar
2Old4This
Regular Visitor
3 years ago

Replace Value based on another column in previous row

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 fie...
Need Help
adudani's avatar
adudani
Icon for Memorable Member rankMemorable Member
3 years ago

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's avatar
    2Old4This
    Regular Visitor
    3 years ago

    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?