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?