Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Changing data type from number to text adds trailing zero

I have a weird issue where I sometimes get trailing zeros when changing the data type from decimal number to text. This is happening in Power Query with Power BI Desktop.    I have a number column ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    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
    Maggie
    Community 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.
peggysue's avatar
peggysue
Advocate I
3 years ago

Hi everyone, I found an easy fix for this here: https://www.nickyvv.com/2021/12/power-query-trick-remove-only-leading-trailing-zeroes-from-text.html

 

The Microsoft doc for Text.TrimEnd doesn't say this very clearly, but you can specify what character you want to trim from the end of your text string.

 

Here's the formula that worked for me:

= Table.AddColumn(#"convert to text", "Text.TrimEnd :)",
each if Text.Contains([Text From Rounded Number], ".") then Text.TrimEnd([Text From Rounded Number], "0")
else [Text From Rounded Number], type text)

 

You'll see that I had to add a Text.Contains to check for a decimal point before trimming. This keeps it from completely blanking out the value that is exactly 0.