Dynamic Datatype from Text to Number conversion is different Localization per Row

[padinator]

Hello there,i have a power query which is updating excel files placed within a specific folder.

The layout of these files is always identical - therefor i can easily set the appropriate steps neeed in order to format the document as needed.

My problem is, that there is a Value Column (Quantity) wihch seems to be differently formatted within the particular documents.

Sometimes it has a DOT (100.00) as separator and sometimes there is a comma (100,00) - example given for ONE HUNDRET.

Now i am wondering to somehow dynmically identify the format used and adapt it accordingly because at the moment as i set en-US as localization i get something like 10000000000 for 100,00 and if i set de-DE i get 1000000000 for 100.00 .

In addition to that i found out that sometimes a thousand-separator is used (e.g. 1.000,00) or (e.g.

For any further questions please let me know! Any help is highly appreciated ! !

[Anonymous]

Hi @padinator ,

Any updates? Does @wdx223_Daniel 's method help you?

Please Accept a helpful reply as the solution to make this thread closed. Other members could find it more quickly.

Best Regards,
Eyelyn Qin

[wdx223_Daniel · Accepted solution]

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

[smpa01]

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

[padinator]

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

[ronrsnfld]

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.

[padinator]

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.