Forum Discussion
Dynamic Datatype from Text to Number conversion is different Localization per Row
assume all the values in this column is integer, then try this code
NewStep=Table.TransformColumns(PreviousStepName,{"Quantity",each let a=Text.SplitAny(_,".,") in Number.From(Text.Combine(a))/(if Text.Length(List.Last(a))=2 then 100 else 1)})
Is there no way that you can obtain the files with that column as a number rather than as text? If not, you'll have to create a custom column that somehow analyzes the various possible combinations of delimiters and determines which translation to use. But I don't know how to take a column with, let us say, three decimals, (eg 100.123) and with just that information, determine whether the dot is to be considered a decimal or a thousands separator.
well, i guess i will have to open each file and convert the column to number from text because this is basically the problem. There are rows looking like 1.001,2 other like 50,4 (without the dot as thousand-separator) or 4.3 and this column being initially set as text i get huuge numbers out after converting to number idk why.