cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Goodkat
Frequent Visitor

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 to replace several values with 'null'. The replacement works, but it turns back blancs, not nulls.

A workaround could be to have a blanc by null replacement following, but I would like to understand were I made a mistake in adjusting Rick's methodology in my environment.
Here a sample file for testing https://c.gmx.net/@324888734501700174/wRcVKVRsSmKqLp_ncGUHdA
That is the code that returns blancs instead of desired nulls

 

ReplaceBatch_PersonResp = Table.ReplaceValue(Quelle,
each [Responsible],
each Text.Combine (List.ReplaceMatchingItems({[Responsible]},{{"#", null}, {"n.n.", null}, {"na", null}}, Comparer.OrdinalIgnoreCase)),
Replacer.ReplaceValue,{"Responsible"})

 

I would be grateful if someone could shed light on this, so that I can also learn, were my understanding is still insufficient.

Thank you!

 

Best regards, Andreas aka 'Goodkat'

2 ACCEPTED SOLUTIONS
Vijay_A_Verma
Super User
Super User

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"})

View solution in original post

Smauro
Solution Sage
Solution Sage

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,




Feel free to connect with me:
LinkedIn

View solution in original post

6 REPLIES 6
AlexisOlson
Super User
Super User

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"})

 

Smauro
Solution Sage
Solution Sage

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,




Feel free to connect with me:
LinkedIn

Goodkat
Frequent Visitor

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

Goodkat
Frequent Visitor

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"

Vijay_A_Verma
Super User
Super User

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"})

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors