Forum Discussion

Marpas's avatar
Marpas
Helper I
1 year ago

can we have Any to Text conversion fixed?

As title says, converting from any to text leads to messed up number.
In my case it's 287.1 (any) gets converted to 287.100000000000002 (text).
Issue exists for more than 5 years already, can we have it fixed?
Solved: Re: Changing data type from number to text adds tr... - Microsoft Fabric Community
And no, it's not solved, I've got field with plenty of not numeric text ("ABC123"), integer numbers ("123"),  decimal numbers ("123.1") and combination of all ("123 & 234.1 & ABD123"), so provided workaround wont work.

 

Need Help

6 Replies

  • ZhangKun's avatar
    ZhangKun
    Super User
    1 year ago

    This is the storage mechanism of floating point numbers inside the computer and cannot be easily modified. Although there is a Decimal type in Power Query that can accurately represent decimals, this type only exists inside the calculation and cannot be represented externally (can be passed to DAX). For your problem, you may try to nest a Decimal.From function outside the number.

     

    Text.From(Decimal.From(0.1+0.2)) & "AAA"

     

    • Marpas's avatar
      Marpas
      Helper I
      1 year ago

      what would be a result of this function if input is "123 & 234"?

      • ZhangKun's avatar
        ZhangKun
        Super User
        1 year ago

        Sorry for ignoring your reply. "123&234" cannot be used as input because it is a string. If multiple inputs are required, additional judgments can only be added. Also, I have found a confusing question, why is your result different from mine, a version issue?

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Marpas ,

    Maybe you can try to use this M code to create a custom column:

    #"Added column"= Table.AddColumn(Source, "TransformedData", each 
    let
        originalValue = [MixedData],
        isNumeric = try Number.FromText(originalValue) otherwise null,
        transformedValue = if isNumeric <> null then Text.From(Number.Round(isNumeric, 15)) else originalValue
    in
        transformedValue,
        type text
)

     

    However, I don't know how you got this column, so I can't test to ensure the correctness of my answer, because manual data entry generally won't cause your problem.


    So if our answer cannot solve your problem, please provide the original data and tell us how you got this column with type any. Thank you!

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Marpas's avatar
      Marpas
      Helper I
      1 year ago

      Hi,

       

      I might give it a try. I still stand with my claim that we shouldn't be forced to make extra steps (or even extra columns) for something as simple as double to string conversion. Microsoft has figured it out 30 years ago in vba, I dont reason why we should tolerate it in paid product now.

      Regarding the column itself, it is manual input, users can put whatever they need, it serves as an ID which could be either integral, decimal or string.

      It's imported as Any and I would prefer to keep it as string (as it should be treated this way), but it's power query that fails doing basic conversion.

      before conversion (Any)

      after conversion (Text)

       



      meantime VBA from 1994:

       

      edit:

      Acutally I was thinking about just leaving it as Any (variant) and do Text (string) conversion later on, but apparently I can't have that as dataflow will force conversion to Text (it will add it as a step if not there).