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

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.

hanging data type from number 2.pbix34 KB

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

hanging data type from number to text adds trailing zero.pbix29 KB