Fix Excel Power Query Displaying Chinese Column Headers as Question Marks
Question details
The user needs to fix an issue where Chinese column headers imported from an Oracle database display as question marks in Excel Power Query, causing query errors.
- Product
- Excel LTSC 2021
- Device & OS
- Windows
- Scenario
- Importing data from an Oracle database containing Chinese characters using the Excel Power Query feature.
- Observed behavior
- Chinese column headers render as garbled text (question marks), which leads to 'column not found' errors when the query executes.
Ensure you have administrator privileges on your computer, as fixing this issue requires adding or modifying system environment variables and regional settings.
Configure the Oracle NLS_LANG Environment Variable
Set the correct language and encoding environment variable for Oracle databases to ensure Chinese characters are interpreted correctly by Excel.
This issue is typically caused by a missing or misconfigured Oracle NLS_LANG environment variable rather than an internal Excel or Power Query bug. Setting this variable tells the Oracle client how to encode the data being sent to Excel.
Press the Windows key, type 'Environment Variables', and select 'Edit the system environment variables'.
In the System Properties window under the Advanced tab, click on the 'Environment Variables' button at the bottom.
Under the 'System variables' section, click 'New'.
Enter 'NLS_LANG' as the Variable name and 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK' as the Variable value, then click 'OK' to save.
Restart your computer or the relevant database services to apply the changes. Open Excel, go to the Data tab, and refresh your Power Query.
Enable the Beta Feature for Worldwide Language Support
Use this Windows settings workaround if configuring the Oracle variable does not fully resolve the text encoding issue.
Experience Hassle-Free Data Management with WPS Office
If you frequently encounter complex environment variable issues or update-related bugs in Excel, consider switching to WPS Office. It provides a highly compatible, easy-to-use alternative for handling your data efficiently without steep learning curves or constant troubleshooting.
- 1. Download the Installer: Visit the official WPS website to download the free installation package for your PC.
- 2. Install WPS Office: Run the installer and follow the quick setup instructions to configure WPS Office on your computer.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing Microsoft Excel files to resume your work instantly with full format retention.
Frequently Asked Questions
Why does Excel Power Query show question marks instead of Chinese characters?
This happens due to a text encoding mismatch between the data source (like an Oracle database) and Microsoft Excel. If the environment variable specifying the database character set is missing or incorrect, Excel cannot decode the text and substitutes unrecognized characters with question marks.
Can Windows updates cause Power Query garbled text issues?
Yes. Some users report that automatic Windows updates can reset or alter how the operating system handles system locales and regional settings. This can suddenly cause text encoding issues in applications like Excel that previously worked perfectly.
How do I refresh my Power Query after fixing the environment variable?
After restarting your computer to apply the new system variables, open your Excel workbook, navigate to the 'Data' tab on the top ribbon menu, and click 'Refresh All'. This forces Power Query to reconnect to the database and fetch the data using the newly applied text encoding.
