Fix Latitude and Longitude Decimal Formatting in Excel Power Query
Question details
The user needs to correct decimal and thousands separator interpretation for imported latitude and longitude values.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Data imported from Denmark uses periods and commas differently from the format required by mapping tools such as Tableau.
- Observed behavior
- Latitude and longitude values are interpreted with incorrect decimal formatting after import.
Check a few source values to identify which character represents the decimal separator and which represents the thousands separator. Close or note the affected query so you can recreate or refresh it after changing the regional setting.
Set Excel Power Query regional settings to US English
Use this method when imported coordinates have incorrect decimal or thousands separators because the source locale differs from the format required by your mapping tool.
Power Query uses regional settings to interpret numeric text during import. Selecting US English makes periods the decimal separator and commas the thousands separator, which can prevent latitude and longitude values from being converted incorrectly.
In Excel, open the Data tab, select Get Data, and choose Query Options.
In the Query Options window, select Regional Settings, open the locale list, and choose English (United States) or US English.
Select OK, then recreate the import query or return to Excel and use Data > Refresh All to reload the data with the new locale.
In Power Query or the worksheet, verify that latitude and longitude columns are recognized as Decimal Number values and display periods as decimal separators.
Edit and validate coordinate data with WPS Office
WPS Office is a free, lightweight Microsoft Office alternative for opening, reviewing, and editing coordinate spreadsheets. It supports common Excel formats and offers a familiar interface for checking decimal formatting before exporting data to mapping tools, although it does not provide Microsoft Excel Power Query's regional-setting feature.
Frequently Asked Questions
Where are Power Query regional settings located in Excel?
Open Excel and go to Data > Get Data > Query Options. Select Regional Settings, then choose the locale that matches the decimal and thousands separators in your source data.
Why do Danish latitude and longitude values import incorrectly?
Danish data commonly uses a comma as the decimal separator and a period as the thousands separator, while many mapping tools expect the US convention: a period for decimals and a comma for thousands.
Should I refresh the query or create it again after changing the locale?
You can try Data > Refresh All first. If the existing query still interprets the values incorrectly, recreate the query after selecting US English in Query Options > Regional Settings.
How can I confirm that the coordinates were converted correctly?
In Power Query, select each coordinate column and check that its data type is Decimal Number. Then inspect sample values in Excel and confirm that the decimal separator matches the format required by Tableau or your mapping tool.
