can we have Any to Text conversion fixed?

[Marpas]

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.

[Anonymous]

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]

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

[ZhangKun]

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]

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

[ZhangKun]

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?

[Marpas]

Hi ZhangKun,

No worries.

Unfortunately, I can't restrict what users would put as data. Plenty of records have value of "123 & 234" or similar and they intent to keep it this way for a while.

Why it works fine for you but not for me? not a clue, makes no sense, seems like bug to me.

Or you are checking it in Power BI Desktop / Transform Data (Report's semantic model), while I have this issue in Power BI Service / Dataflow gen1.

inb4: if Semantic model converts Any to Text just fine, why not skip conversion in Dataflow and do it in Semantic model instead?
I've tried that, but Dataflow forces conversion as last step of table transformations. If that step is deleted, it will be recreated automatically during close.