Clear all cells within a table that contain a particular string

[onedayover]

Hi All

Would anyone know how I could change the code below to clear all cells within a table that have that same string and not just a single column?

Example...
= Table.ReplaceValue(#"Extract Instance", each [syllabus], each if Text.Contains([syllabus], "claimed") then "" else [syllabus], Replacer.ReplaceText, {"syllabus"})

[wdx223_Daniel]

=Table.TransformColumns(#"Extract Instance",{},each  if Text.Contains(_, "claimed") then "" else _)

[jbwtp]

Hi all,

I think this is the way to do it if we need to clear all other cells in a row that contains claimed in [syllabus]:

Table.ReplaceValue(#"Extract Instance", each [syllabus], null, (x, y, z)=> if y = "claimed" and x <> "claimed" then "" else x, Table.ColumnNames(#"Extract Instance"))

I am not sure however about the performance of the above code on large sets of data, theoretically, it looks through and resolves every cell in the table, which should (in tehory) be quite CPU consuming. May be test it against filtering out the "claimed" rows, delete all other columns and append back to the "unclaimed" portion of the table as suggested by @ImkeF.

And this one removes all cells that contains calimed leaving all other cells as they were:

Table.ReplaceValue(#"Extract Instance","claimed","",Replacer.ReplaceValue,Table.ColumnNames(#"Extract Instance"))

Kind regards,

John

[onedayover]

Hi John
My apologies, I don't think I explained this well. In my table (courses), the code I provided clears any cell within the column "syllabus" where the value "claimed" appears. I'm needing to change this code to clear any cell, within the entire table (not just the column "syllabus" and not the entire row), where the value "claimed" appears. "Extract Instance" belongs to the last step in my code.

I did try your second example (Table.ReplaceValue(#"Extract Instance","claimed","",Replacer.ReplaceValue,Table.ColumnNames(#"Extract Instance")), however, I wasn't able to get it to work.

I'm not entirly sure if what I am asking is possible, however I hope this clarifies better.

I appreciate your response.

[jbwtp]

Hi @onedayover,

Sorry, my fault, because I gave you a code that clears the cells that equal to claimed exactly, not the ones that contain it.

This is the version to solve the contains scenario:

Table.ReplaceValue(#"Extract Instance", each [syllabus], null, (x, y, z)=> if Text.Contains(x, "claimed") then "" else x, Table.ColumnNames(#"Extract Instance"))

Cheers,

John

[onedayover]

This works perfectly, thank you John!

[jbwtp · Accepted solution]

Actually, this is probably more correct and uniform to the template:

Table.ReplaceValue(#"Extract Instance", "claimed", "", (x, y, z)=> if Text.Contains(x, y) then z else x, Table.ColumnNames(#"Extract Instance"))

Cheers,

John

[v-cgao-msft]

Hi @onedayover ,

Please try:

= Table.ReplaceValue(#"Extract Instance","claimed","",Replacer.ReplaceText,{"syllabus", "Column2", "Column3"})

and replace "Column2", "Column3" with all field names in the table.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[onedayover]

Hi Gao, thanks for getting back to me. I did try it however the code caused errors in all columns when applied. I'm trying to only clear the values from those cells in the table that contain a particular word - that being "claimed" in this example.

[ImkeF]

Hi @onedayover ,
if I understand the problem correctly, I originally thought that replacement on all table columns like so would work:

Table.ReplaceValue(#"Extract Instance", each [syllabus], each if Text.Contains([syllabus], "claimed") then "" else [syllabus], Replacer.ReplaceValue, Table.ColumnNames(#"Extract Instance") )

... but it didn't 🙂
Couldn't find a way to make it work with the Table.ReplaceValue-function. So you might want to go with filtereing out those rows completely instead and replace them with empty rows (whatever you want to achieve with that operation..)