Forum Discussion
Solved
Power 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(_,".")
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"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(_,".")
Hi vanessafvg,
Please try the Text.TrimEnd function as MarcelBeug posted. Please feel free to ask if you have any other issue.
Thanks,
Angeliaworks like a charm thanks MarcelBeug
Much more elegant than what I was doing. Thank you MarcelBeug
