Replacing values in specific cells

[Anonymous]

Hi

I'm running into a strange issue.

This works:

#"Replaced Value28" = Table.ReplaceValue(#"Replaced Value27","","BLANK",Replacer.ReplaceValue,{"some_type"}),
#"Replaced Value29" = Table.ReplaceValue(#"Replaced Value28","BLANK", each if [db_id]="1234" then "BLABLA" else "BLANK" ,Replacer.ReplaceText,{"some_type"}),

And it works with multiple rows which were empty before. I just added BLANK and it worked. It didn't work when I tried using the "" to just replace. Had to add BLANK.

But when I do this:

#"Replaced Value73" = Table.ReplaceValue(#"Replaced Value72","Some value", each if [submitid]="1234" then "Some other value" else "Some value" ,Replacer.ReplaceText,{"some_type"}),

It doesn't actually replace any values.

I've also looked into the DB and the value that I'm replacing is the same. Copied it straight from the DB. What could be causing this or what can I add?

I need to replace specific values with specific ID-s in the row with other specific values.

Thanks!

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

Please check whether the data type of column [submitid] is text, or you can modify your formula like this:

#"Replaced Value73" = Table.ReplaceValue(#"Replaced Value72",each[some_type], each if [submitid]="1234" then "Some other value" else "Some value" ,Replacer.ReplaceText,{"some_type"}),

Addtionally, by my test, in your below step, if "some_type" column is of text type, it works fine. But if it is number type, the "" in the formula should be replaced by null.

#"Replaced Value28" = Table.ReplaceValue(#"Replaced Value27","","BLANK",Replacer.ReplaceValue,{"some_type"})

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Vijay_A_Verma]

Your formula is allright.

To check it, can you run following and see whether you get true result. This will prove whether the string exists or not. Check into rows against [submitid]="1234"

=Text.Contains(["some_type"],"Some value")

[Anonymous]

The problem was in somewhere else in the syntax. This code actually works.