TRIM text and _

[sdhn]

Hi All,

I have list of items in  a table as below:

LON_HOME

LON_ADDRESS
LON_PERSON
LON_OWNER  etc

I want data appears in the report as:  

HOME
ADDRESS
PERSON
OWNER

I am using Power Bi desktop version.

Thanks 

[v-yiruan-msft]

Hi @sdhn ,

You can copy and paste the following codes in your Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vH3i/fw93VVitWBcBxdXIJcg4Ph/ADXoGB/PzjXP9zPNUgpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Trimmed Text" = Table.TransformColumns(Source,{{"Text", Text.Trim, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Trimmed Text","LON_","",Replacer.ReplaceText,{"Text"})
in
    #"Replaced Value"

Best Regards

[Anonymous · Accepted solution]

There can be many ways to do it.

1. In power query replace LON_ to blank.

2. You can split the column in Power query by number of delimiters as 4 as far left as possible.