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 with for example the value 0.00785 in it. I then round it off like so: 

 

= Table.TransformColumns(#"Filtered Rows",{{"Verkoopfactuur_prijs", each Number.Round(_, 4, 0), type number}, {"Verkoopfactuur_inkoopwaarde", each Number.Round(_, 2, 0), type number}})

Which gets me  0.0079 as expected.

 

I then change the data type to text with this code:

 

= Table.TransformColumnTypes(#"dwh Vervang Null waarden", List.Transform(Table.ColumnNames(#"dwh Vervang Null waarden"), each {_, type text}))

 

I would expect the value to stay the same, however it changes to 0.00790.  This trailing zero gets added for several values, but not for all. Any thoughts on how to tackle this issue?

 

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.

13 Replies

  • Aivars's avatar
    Aivars
    Frequent Visitor
    6 years ago

    I have the same issue - 2,2 turns to 2,2000000000000002 when converted to text in query editor.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Worse if you convert "2.1" to text you get: 2.0099999999999998 - so you can't just trim off characters to get the right answer.

       

      I assume under the hood floating points numbers are being converted to float64 format to get these type of rounding errors (I'm importing from Excel in this case).

       

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    Modify the code in Advanced editor as below

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PcjRCcAwCAXAXfwOxWp86izB/dcItSF/x61F/DB7GI1Pogaq8S88e0WZ5W7G26vT4GcDU6U3HJlUtQE=", 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}}),
    #"Extracted First Characters" = Table.TransformColumns(#"dwh Vervang Null waarden", {{"Verkoopfactuur_prijs", each Text.Start(Text.From(_, "en-US"), 6), type text},{"Verkoopfactuur_inkoopwaarde", each Text.Start(Text.From(_, "en-US"), 4), type text}})
in
    #"Extracted First Characters"
    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.

    sds

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-juanli-msft ,

       

      Thanks for your answer! However your solution sadly doesnt fit the requirement. Numbers in my column can also have values of let's say 49.0493, it's not always 6 characters. 

      I was thinking that as a workaround I could check on which position the last zero is in the string and if that is the same as the total length of the string it needs to be deleted. Have not got that working though. 

       

      Also would ofcourse much prefer a solution instead of a workaround. :)

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Have still not found a working workaround or solution. Any help would be appreciated.  

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      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.
  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    Is this problem sloved? 
    If it is sloved, could you kindly accept it as a solution to close this case?
    If not, please let me know.
     
    Best Regards
    Maggie
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Your workaround seems to work. I would still like a solution instead of a workaround, but I will accept the answer for now. 

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        6 years ago

        Hi Anonymous 

        Thanks.

        I would report this issue to MS, If it is accepted and would be fixed or provided with any solution, i will tell you.

         

        Best Regards
        Maggie
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Has this extremely annoying behaviour been solved yet? It's happening to me now. 

  • 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.

     

     

  • ce_PowerPlay's avatar
    ce_PowerPlay
    Regular Visitor
    6 months ago

    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