Replace multiple words in a row

[massotebernoull]

Hello,

I'm struggled with this replacement:

I have this table:

Original	TRY1	TRY2
	Non-qualified	Non-qualified
	Non-qualified	Non-qualified
	Non-qualified	Non-qualified
100000010,100000014	Verify	100000010,100000014
100000010,100000014	Verify	100000010,100000014
100000011	Declared bankruptcy	Declared bankruptcy
100000011	Declared bankruptcy	Declared bankruptcy
100000011	Declared bankruptcy	Declared bankruptcy
100000010	International school	International school
100000014	Filantropic school	Filantropic school
100000010	International school	International school
100000010,100000014	Verify	100000010,100000014
100000010,100000014	Verify	100000010,100000014
100000010,100000014	Verify	100000010,100000014
100000010,100000014	Verify	100000010,100000014
100000014	Filantropic school	Filantropic school

I have to create a column that replace all values for texts. The TRY1 I tried by Conditional Column, like

each if [Original] = "100000011", then "Declared bankruptcy", 
else if [Original] = "100000014" then "Filantropic school" 
... 
else "Verify"

And in the TRY2 I tried by custom column, like

Table.AddColumn(#"Added Custom2", "Justificativa3", each Text.Combine(
List.ReplaceMatchingItems(
{[new_justificativa1]},
{
{"100000011", "Declared bankruptcy"}, 
{"100000014", "Filantropic school",
...
{null, "non-qualified"}
 },
Comparer.OrdinalIgnoreCase)))

But it didn't work either. 

Is there a way to create a column replacing all lines, including the ones with "100000010,100000014"? This is just an example, I have a lot of words to replace and if I create one step for each replacement, my pbix is going to explode..my table have more than two values in a row and more different values.

Best regards

[AlexisOlson]

List.ReplaceMatchingItems is a good idea but it's not easy to have an else condition with that, so I'd suggest the following:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlCK1UElDQ3AwNBAB8YyIUrckGieAQrPBI8cqW4gVRzIiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Original = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Original", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each
        Text.Combine(
            List.Transform(
                Text.Split([Original], ","),
                each
                    if      _ = "100000011" then "Declared bankruptcy"
                    else if _ = "100000014" then "Filantropic school"
                    else if _ = ""          then "non-qualified"
                    else "Verify"
            ),
            ","
        )
    )
in
    #"Added Custom"

[massotebernoull]

Thanks for your reply. It worked fo the lines with multiple itens, but it didn't work for the lines wich have "null" values. I'm sorry I miss specified instead of replace "" to "Non-qualified", I want to replace null to "Non-qualified". When I use your formula, I get error in null values. Is there a way to do this whitout adding more steps?

[AlexisOlson]

If you have actual nulls rather than empty strings, then @mussaenda's suggestion is correct.

Update the custom column definition to this:

if      _ = null        then "Non-qualified"
else if _ = "100000011" then "Declared bankruptcy"
else if _ = "100000014" then "Filantropic school"
else "Verify"

[massotebernoull]

It didn't work either

It shows

"Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=[Type]"

[AlexisOlson · Accepted solution]

Ah, I think the issue must be earlier then.

Try putting the null check in earlier:

Text.Combine(
    List.Transform(
        Text.Split(if [Original] = null then "" else [Original], ","),
            each
                if      _ = "100000011" then "Declared bankruptcy"
                else if _ = "100000014" then "Filantropic school"
                else if _ = ""          then "non-qualified"
                 else "Verify"
        ),
        ","
    )
)

[massotebernoull]

This one works very well. Thank you!

[mussaenda]

Hi @massotebernoull ,

from the reply of @AlexisOlson ,

You can replace "" to null and position it as the first argument.