Changing data type from number to text adds trailing zero

[Anonymous]

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?

[peggysue]

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.

[Anonymous]

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

[Aivars]

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

[Anonymous]

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]

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]

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]

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]

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

[v-juanli-msft · Accepted solution]

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]

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]

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

[AnkitBI]

@v-juanli-msft - That's a WorkAround. Any reason for this behaviour. Why a '0' is getting added on changing from Number to Text.

@ImkeF @Zubair_Muhammad  - Any thoughts

Thanks,

Ankit Jain