Forum Discussion
Changing data type from number to text adds trailing zero
- 6 years ago
Hi Anonymous
Sorry, i can't figure out why leads this problem.
Maybe my previous workaround is not good, please check this one.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TczLCcAwDAPQXXIuxrFkJ56lZP81mk8pvUlPoPsuKqqte7lWMniUcR2NllsNqvZp9roV9Giv9iBsa2+RuZUpygQnI2Xu9c9TCaHTzrPNAoeuARQzehnjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Verkoopfactuur_prijs = _t, Verkoopfactuur_inkoopwaarde = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Verkoopfactuur_prijs", type number}, {"Verkoopfactuur_inkoopwaarde", type number}}), #"dwh Vervang Null waarden"= Table.TransformColumns(#"Changed Type",{{"Verkoopfactuur_prijs", each Number.Round(_, 4, 0), type number}, {"Verkoopfactuur_inkoopwaarde", each Number.Round(_, 2, 0), type number}}), #"Changed Type1" = Table.TransformColumnTypes(#"dwh Vervang Null waarden",{{"Verkoopfactuur_prijs", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Verkoopfactuur_prijs", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Verkoopfactuur_prijs.1", "Verkoopfactuur_prijs.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Verkoopfactuur_prijs.1", Int64.Type}, {"Verkoopfactuur_prijs.2", type text}}), #"Extracted First Characters" = Table.TransformColumns(#"Changed Type2", {{"Verkoopfactuur_prijs.2", each Text.Start(_, 4), type text}}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Extracted First Characters", {{"Verkoopfactuur_prijs.1", type text}}, "en-US"),{"Verkoopfactuur_prijs.1", "Verkoopfactuur_prijs.2"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Verkoopfactuur_prijs") in #"Merged Columns"Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey All, I came accross this thread when I was looking into this issue recently and I found a solution I felt I should share. Before using the function Number.ToText() use the function Double.From() or Decimal.From(). Number.ToText() will provide a more reasonable output or when using Decimal.From() it will actually follow the format provided but that can not be used in direct query mode.
Original Example: Number.ToText(500.1234,"#.#")
Output: 500.12340000000
Double.From Example: Number.ToText(Double.From(500.1234),"#.#")
Output: 500.1234
*This method ignores specified formating and output the number "as is" in text format with no additial 0s added. Direct Query Compatible
Decimal.From Example: Number.ToText(Decimal.From(500.1234),"#.#")
Output: 500.1
*This method follows the specified format, but does not work in Direct Query mode