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,

Smauro's avatar
Smauro
Solution Sage
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,

  • Goodkat's avatar
    Goodkat
    Helper II
    4 years ago

    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