Forum Discussion

NiugeS's avatar
NiugeS
Icon for Helper V rankHelper V
6 years ago
Solved

Removing Text in Added Column

Hi,

 

New to PowerBi.  

I have a column that contains names and the word 'brother' with a '-' and sometimes spacing as per below.  I want to create a new column based on the orginal column but remove the '-' and the word 'brother'.  Sometimes there are '-' within the name.

 

Name A-brother

Name B - brother

Name-Name C-brother

 

Thank you

 

Need Help
  • HotChilli's avatar
    HotChilli
    6 years ago

    Keep it simple by right-clicking the column.  Choose replace values.  Type -brother in the 'value to find' and don't put anything in the other.  Click ok.

    Repeat with - brother

  • dax's avatar
    dax
    6 years ago

    Hi NiugeS , 

    Yes, you could refer to HotChilli 's suggestions, click "Edit Queries", then tranform it by "repalce" like below 

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVVw1E0qyi/JSC1SitWBCjkp6CqgC+qCZZwRimMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [NAME = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NAME", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "NAME", "NAME - Copy"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","-","",Replacer.ReplaceText,{"NAME - Copy"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","brother","",Replacer.ReplaceText,{"NAME - Copy"})
in
    #"Replaced Value1"

     

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Keep it simple by right-clicking the column.  Choose replace values.  Type -brother in the 'value to find' and don't put anything in the other.  Click ok.

    Repeat with - brother

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi NiugeS , 

    Yes, you could refer to HotChilli 's suggestions, click "Edit Queries", then tranform it by "repalce" like below 

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVVw1E0qyi/JSC1SitWBCjkp6CqgC+qCZZwRimMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [NAME = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NAME", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "NAME", "NAME - Copy"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","-","",Replacer.ReplaceText,{"NAME - Copy"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","brother","",Replacer.ReplaceText,{"NAME - Copy"})
in
    #"Replaced Value1"

     

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.