Trouble with decimal commas and decimal dots

[JRVV]

In SQL DB my data appear with decimal commas, but in Power Query that disappear. 12000,5 ---> 120005. Someone can give me some advice, please? 

[Anonymous · Accepted solution]

Hi @JRVV ,

Thanks for the reply from Fowmy .

Power Query may interpret the comma as a thousands separator instead of a decimal point, so the data will change after import.

To fix this you can follow along with my actions.

First, here's the data I used for testing:

Value
12000,5
3456,78
789,01

I can reproduce your error, the comma separator is gone after importing the data into Power Query.

To resolve this issue, first, in the Home tab, click on the Data Type drop-down menu and set the Data Type to Text. This step ensures that Power Query does not automatically convert commas to thousands separators.

Then, use the Replace Value function to replace the comma with a decimal point:

Finally, select the Value column. Click on the “Change Data Type” menu and set the data type to “Decimal Number”.

In this way, Power Query correctly converts decimal values from text format to decimal format.

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Fowmy]

@JRVV 

1. Try to change the data type to Decimal Number
or
2.   Click Transform > Data Type>  Using Locale.
Choose "Decimal Number" and set the locale to the one matching your SQL Server settings (e.g., French (France) or German (Germany) if your SQL uses a decimal comma)

3. If your SQL Server stores numbers as text with commas, convert them before importing:

SELECT REPLACE(CAST(ColumnName AS NVARCHAR(50)), ',', '.') AS ConvertedColumn
FROM TableName