Forum Discussion

Goodkat's avatar
Goodkat
Helper II
4 years ago
Solved

Batch ReplaceValue Text does not deliver 'null'

Dear Powerquery enthusiasts,   By reading posts and searching I was able to find a way (Kudos go to Rick de Groot from Gorilla BI!) to replace in a batch multiple values in one step. Now I wanted t...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    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"})
  • Smauro's avatar
    Smauro
    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,

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

Here's another possibility:

Table.ReplaceValue(Quelle,
    each [Responsible],
    each if List.Contains({"#", "n.n.", "na"}, Text.Lower([Responsible]))
         then null else [Responsible],
    Replacer.ReplaceValue, {"Responsible"})