Forum Discussion
Batch ReplaceValue Text does not deliver 'null'
- 4 years ago
Since this is a single value list, hence Text.Combine is not needed. This is conveting nulls into blanks. Use below formula
= Table.ReplaceValue(Quelle, each [Responsible], each List.ReplaceMatchingItems({[Responsible]},{{"#", null}, {"n.n.", null}, {"na", null}}, Comparer.OrdinalIgnoreCase){0}, Replacer.ReplaceValue,{"Responsible"}) - 4 years ago
You could optimise it a little bit by skipping all the extra info provided and just customise the Replacer funcion:
Table.ReplaceValue( Quelle, {"#", "n.n.", "na"}, null, (x, y, z) as nullable text => if List.MatchesAny( y, each _ = Text.Lower(x) ) then z else x, {"Responsible"} )That way, you do not feed it [Responsible] 3 times per calculation, and the check stops once it finds a match, instead of trying to replace every value and if the value is replaced then it replaces the actual value in the record (row).
Cheers,
You could optimise it a little bit by skipping all the extra info provided and just customise the Replacer funcion:
Table.ReplaceValue(
Quelle,
{"#", "n.n.", "na"},
null,
(x, y, z) as nullable text =>
if List.MatchesAny( y, each _ = Text.Lower(x) )
then z else x,
{"Responsible"}
)
That way, you do not feed it [Responsible] 3 times per calculation, and the check stops once it finds a match, instead of trying to replace every value and if the value is replaced then it replaces the actual value in the record (row).
Cheers,