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:

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

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

7 Replies

  • 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?