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,
Dear Vijay,
thank you for your reply. It works perfectly! It implemented the code and also took it into my personal encyclopedia of PowerQuery learnings for annotating it. By thinking about your code lines I stumbled over the braced 0
Comparer.OrdinalIgnoreCase){0}
What is this for? Is it related to the 'Table.ReplaceValue' part? Maybe if you have two minutes left you could let me know the rationale of the {0}
But already now a big 'thank you' to you for providing solution and much appreciated insight.
Best regards, Andreas
This is to pick up a value from the list on the basis of index. In PQ, index starts with 0.
Hence {0} means I want to pick up first value from the list. If I don't use {0}, it will return the list as an answer. With {0}, the first value gets picked up (in anyway, the list in your case is single value only and that value needs to be extracted)
Hence if list is MyList = {"z","x","a"}, then MyList{0}= "z", MyList{1} = "x", MyList{2}="a"