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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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