Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
2Old4This
Regular Visitor

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 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.

2 REPLIES 2
adudani
Super User
Super User

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..

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

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?

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors