Where to Find Power Query Culture Identifiers in Excel
Question details
The user needs to find a reference list of culture identifiers, such as en-US, to correctly apply regional formatting during data transformation in Power Query.
- Product
- Power Query
- Device & OS
- not provided
- Scenario
- Applying regional settings to properly format data like dates, numbers, and currencies from different international locales.
- Observed behavior
- The user is searching for the official location or documentation containing all valid culture codes supported by Microsoft's Power Query.
Identify the origin country and language of your raw data to ensure you look up the correct regional culture code before applying transformations.
Refer to the Official Microsoft Culture Codes Documentation
You can find all supported culture identifiers on Microsoft's Bing Maps REST Services documentation page, which shares the standard codes used by Power Query.
Power Query uses standard Microsoft culture codes to interpret regional data. Accessing the official documentation is the most reliable way to get an updated list of identifiers for your data transformation tasks.
Open your web browser and navigate to the Microsoft Learn website, or search for 'Microsoft Bing Maps supported culture codes'.
On the documentation page, scroll down to find the 'Supported Culture Codes' table, which lists all valid regional identifiers.
Look for your required language and region combination in the table to get the correct identifier (for example, 'en-GB' for English - United Kingdom or 'de-DE' for German - Germany).
Copy the culture code and paste it into the 'Culture' parameter of your Power Query function, such as Table.TransformColumnTypes, to apply the regional formatting.
Simplify Data Formatting with WPS Office
If you want to avoid complex Power Query codes and quickly format your regional data, consider WPS Office. It provides an intuitive interface for data transformation and is highly compatible with Microsoft formats.
- 1. Open Your Data in WPS Spreadsheet: Launch WPS Office and open your dataset containing the regional values.
- 2. Select the Cells: Highlight the specific columns or cells you want to format.
- 3. Access Format Cells: Right-click the selected area and choose 'Format Cells' from the dropdown menu.
- 4. Set the Locale: Navigate to the 'Number' tab, select your data category (like Date or Currency), and pick the appropriate region from the 'Locale (Location)' dropdown menu.
Frequently Asked Questions
What is a culture identifier in Power Query?
A culture identifier is a specific text code, like 'en-US' or 'fr-FR', used in Power Query to define regional settings. It tells the software how to correctly interpret dates, times, currencies, and numeric formats from different countries.
How do I use a locale when changing data types in Power Query?
When changing a column's data type, right-click the column header, select 'Change Type', and choose 'Using Locale'. A dialog box will appear allowing you to select the specific data type and the region that matches the origin of your data.
Why is my date format returning an error in Power Query?
Errors usually occur when the date format in your raw data (such as DD/MM/YYYY) doesn't match your computer system's default regional settings (like MM/DD/YYYY). Specifying the correct culture identifier or locale forces Power Query to parse the dates correctly without errors.
