Forum Discussion
can we have Any to Text conversion fixed?
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.
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).