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)})
padinator can you please provide a sample data representative of the issue of that single column which has mixed decimal/hundred seperator? ALso, please provide the desired output of that sample data?
I had a somewhat similar situation and I solved it. But I will wait till you provide the data.
Hei and thanks for your message - for me as i mentioned, it seems that it resolved the issue by itself. The problem i have is that we have localization with "," separator for decimals and "." for thousands which would look something like this (e.g. 1.240,00) now as i use the raw data the amount columns are formatted as text but mainly look like the example above - nonetheless the system is formatting the numbers like 1240.00 when they are being imported. What i did is to first set the type to "any" instead of text for this columns when importing and then i specified localization en-US as i finally changed the datatype to number.
I am using this command to handle the datatype conversion correctly
= Table.TransformColumnTypes(#"Umbenannte Spalten",{{"Kosten Einlagerung", type number}, {"Kosten Lager", type number}, {" Kost. Pal.", Int64.Type}, {" Pickkosten", Int64.Type}, {" Cont. Kost.", Int64.Type}, {"Kosten Admin", type number}, {" Kosten Fracht", type number}},"en-US")
Afterwards it looks like this
i really do not understand how and why the system is automatically converting the amount (as text formatted) columns from e.g. 6,9 to 6.9 when importing and then as i would change the datatype to Int64 i would get a huge number without the en-US at the end. Strange enough it does it only for certain sheets
