TextTrimEnd Query - Remove only 1 ending underscore

[ErikPettersson]

Hi,

I'm trying to trim the end of ID's with 2 distinct traits:

104297_190__

110201_130_L_

I want to remove the ending underscore and used the following formula: Text.TrimEnd(_,"_") and got the following result:

104297_190

110201_130_L

However, the result I wanted was the following (i.e. only removing the final underscore, not all ending underscores):

104297_190_

110201_130_L

Is there a way around this? 

Any help is much appreciated

[Interkoubess]

Hi @ErikPettersson,

Are you writing correctly the formula for Text.trimEnd? Because I tried with the same data and I called my Column column1 and it worked.

Check formula below;

Text.TrimEnd([Column1],"_")

Let us know if it does not work again

Ninter

[ErikPettersson]

Ok, how are you writing the query? This is how I write it and I get error now. External_Ref_Edge is the column Im trying to trim

Table.TransformColumns(#"Added Custom",{{"External_Ref_Edge", each Text.TrimEnd([External_Ref_Edge],"_"), type text}})

[Interkoubess]

Please creat a column and enter the formula I gave.

Thank you.

Ninter

[Greg_Deckler]

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMbI0jze0NIiPV4rVAQoYGhgZGMYbGhvE+wBFYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1.2"})
in
    #"Removed Columns"