Power Query Question on removing last character if a certain value is present

[vanessafvg]

I have a column that has 1.02 20.1 etc in, but someone has inputted some of them like 1.02. and 52.01

I only want to remove a . if it is the last character, any idea on how to do that?

[Greg_Deckler]

Here is one way:

let
    Source = Csv.Document(File.Contents("C:\temp\powerbi\decimals.csv"),[Delimiter=",", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "Value", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"Value.1", "Value.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type number}, {"Value.2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [Value.2] = null then [Value.1] else [Value.1] + [Value.2]/10),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value.1", "Value.2"})
in
    #"Removed Columns"

[MarcelBeug · Accepted solution]

Maybe you can use trim to trim all trailing dots.

On the Transform tab, select your column and choose Format - Trim,

Then adjust the code and change

Text.Trim

to:

each Text.TrimEnd(_,".")

[charleshale]

Much more elegant than what I was doing.  Thank you @MarcelBeug 

[vanessafvg]

works like a charm thanks @MarcelBeug

[v-huizhn-msft]

Hi @vanessafvg,

Please try the Text.TrimEnd function as @MarcelBeug posted. Please feel free to ask if you have any other issue.

Thanks,
Angelia