Forum Discussion

sdhn's avatar
sdhn
Responsive Resident
5 years ago
Solved

TRIM text and _

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 u...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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