Forum Discussion
vanessafvg
Community Champion
Community ChampionPower Query Question on removing last character if a certain value is present
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?
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(_,".")
5 Replies
- 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
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(_,".")
v-huizhn-msftMicrosoft Employee
Hi vanessafvg,
Please try the Text.TrimEnd function as MarcelBeug posted. Please feel free to ask if you have any other issue.
Thanks,
Angelia
