Forum Discussion
Batch ReplaceValue Text does not deliver 'null'
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"})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,
Dear Smauro, dear Vijay,
thank you for your replies. With Smauro's approach a total new way appeared. Both ways work in my dataset now. I also took your replies into my learning document and commented them for me, in order to understand better. But the more I see the more I get the impression that the possibilities in M appear to be endless! Absolutely crazy what is all possible! And I still know so little only ;(
Thank you both for letting me participate a little bit in your vast experience & knowledge!
Have a great weekend, wherever you are!
Best regards, Andreas