Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply

Replace multiple words in a row

Hello,

I'm struggled with this replacement:

 

I have this table:

OriginalTRY1TRY2
 Non-qualifiedNon-qualified
 Non-qualifiedNon-qualified
 Non-qualifiedNon-qualified
100000010,100000014Verify100000010,100000014
100000010,100000014Verify100000010,100000014
100000011Declared bankruptcyDeclared bankruptcy
100000011Declared bankruptcyDeclared bankruptcy
100000011Declared bankruptcyDeclared bankruptcy
100000010International schoolInternational school
100000014Filantropic schoolFilantropic school
100000010International schoolInternational school
100000010,100000014Verify100000010,100000014
100000010,100000014Verify100000010,100000014
100000010,100000014Verify100000010,100000014
100000010,100000014Verify100000010,100000014
100000014Filantropic schoolFilantropic 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

1 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"
        ),
        ","
    )
)

View solution in original post

7 REPLIES 7
AlexisOlson
Super User
Super User

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"

 

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?

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"

 

It didn't work either

It shows

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

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

This one works very well. Thank you!

Hi @massotebernoull ,

 

from the reply of @AlexisOlson ,

 

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.