Forum Discussion

massotebernoull's avatar
massotebernoull
Icon for Helper I rankHelper I
4 years ago
Solved

Replace multiple words in a row

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 ...
Need Help
Show and Tell
Tips and Tricks
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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"
        ),
        ","
    )
)
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

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's avatar
    massotebernoull
    Icon for Helper I rankHelper I
    4 years ago

    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?

    • mussaenda's avatar
      mussaenda
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Hi massotebernoull ,

       

      from the reply of AlexisOlson ,

       

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

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      4 years ago

      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's avatar
        massotebernoull
        Icon for Helper I rankHelper I
        4 years ago

        It didn't work either

        It shows

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