Forum Discussion
Convert Special Characters Coma (,) to Point (.) only to errors
I am traying to correct the error where I have ( ,) and (. ) in my colum FC gross amount column. I want to repleace the
(,) to (.) and disppear the (,) because I need to convert from text to number the column in order to do measures and make my query connect.
I want to combine two types of data the "correct" (-6,570.54) and those that are making my error "incorrect" (-74.505,74)
snnpnnncnn and sncnnn.nn in the same file for example
-74.505,74
-6,570.54
I have tried:
- replace values . with nothing
- replace values , with .
- change type to fixed decimal number
And it works for this case -74.505,74
BUT -6,570.54 changes to -657054 it erase the decimal
Case 1 (Errors -74.505,74)
Case 2 (Correct number -6,570.54) When using solution to error
Hi Anonymous ,
You can consider pressing '.' Split the columns so that you get two columns, one for the integer part and one for the decimal part, replacing the ',' in the decimal part and finally combining them into one column.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jXTMTU30DM1UYrVAfLMTfRMDUx1zIHcWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable 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.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Column1.1", "Column1.2"}), #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter",",","",Replacer.ReplaceText,{"Column1.2"}), #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each [Column1.1] & "." & [Column1.2]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1.1", "Column1.2"}) in #"Removed Columns"Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
1 Reply
Hi Anonymous ,
You can consider pressing '.' Split the columns so that you get two columns, one for the integer part and one for the decimal part, replacing the ',' in the decimal part and finally combining them into one column.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jXTMTU30DM1UYrVAfLMTfRMDUx1zIHcWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable 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.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Column1.1", "Column1.2"}), #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter",",","",Replacer.ReplaceText,{"Column1.2"}), #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each [Column1.1] & "." & [Column1.2]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1.1", "Column1.2"}) in #"Removed Columns"Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data