You see garbled or incorrect characters when importing a CSV file into Excel’s Power Query. This happens because the file’s text encoding does not match the encoding Power Query expects. The text appears as strange symbols or question marks. This article explains why encoding mismatches occur and provides steps to fix them.
You can resolve garbled text by specifying the correct file encoding during the import process. The fix involves changing a single setting in the Power Query Editor. You will learn how to identify the right encoding and apply it to your data source.
Key Takeaways: Fixing Garbled Text in Power Query
- Data Source Settings > Change File Origin: Sets the text encoding for a CSV file before loading it into the Power Query Editor.
- Power Query Editor > Transform File Encoding: Corrects garbled text after the file is already loaded by reinterpreting the column data.
- UTF-8, Windows-1252, ISO-8859-1: Common encoding types; selecting the correct one displays characters properly.
Why CSV File Encoding Causes Garbled Text
A CSV file is a plain text file. Text encoding is the set of rules that maps each character to a specific number for storage. Common encodings include UTF-8, which supports many international characters, and Windows-1252, used for Western European languages.
Power Query, by default, often assumes a file uses a system-specific encoding like Windows-1252. If your CSV was saved with a different encoding, such as UTF-8, the numeric values are misinterpreted. This mismatch results in garbled text where characters like “é” or “ä” display as “é” or “ä”.
Identifying the Source of the Encoding Problem
The problem usually originates from the software that created the CSV file. Applications like Notepad, database exports, or web downloads can save files with various encodings. You may not control the file’s source, but you can control how Power Query reads it. Recognizing common garbled patterns can help identify the correct encoding to use.
Steps to Change File Encoding During CSV Import
The most effective method is to set the correct encoding when you first import the data. This prevents garbled text from ever appearing in your query.
- Start the import from the Data tab
In Excel, go to Data > Get Data > From File > From Text/CSV. Navigate to and select your CSV file. - Open the file preview dialog
A preview window opens, showing the garbled text. Do not click Load yet. - Change the File Origin setting
At the bottom of the preview window, find the “File Origin” dropdown. It may currently say “65001 : Unicode (UTF-8)” or something similar. Click the dropdown. - Select a different encoding
Choose another encoding from the list. Common options to try are “1252 : Western European (Windows)” or “ISO-8859-1 : Western European (ISO)”. The preview will update instantly. - Verify the text is correct and load data
Check the preview. If the text looks correct, click Load. If not, try another encoding from the dropdown until the characters display properly.
Fixing Encoding in an Existing Power Query
If you have already loaded a query with garbled text, you can fix it within the Power Query Editor.
- Open the Power Query Editor
Go to Data > Queries & Connections, right-click your query, and select Edit. - Transform the column encoding
Select the column with the garbled text. Go to the Transform tab. Click the Data Type dropdown icon next to the column name in the ribbon, not in the header. - Choose “Using Locale…”
From the dropdown, select Using Locale. In the dialog that opens, set “Data Type” to Text. Then, use the “Locale” dropdown to select a region that matches your file’s encoding, like “English (United States)” for Windows-1252. - Apply and save changes
Click OK. The column data will be reinterpreted. Click File > Close & Load to apply the changes to your worksheet.
If Changing Encoding Does Not Fix the Text
Power Query Still Shows Garbled Characters
If you have tried all common encodings and the text remains garbled, the file might be corrupted or use a very rare encoding. Try opening the CSV file in a simple text editor like Notepad. In Notepad, go to File > Save As. Before saving, look at the “Encoding” dropdown at the bottom of the Save As dialog. This shows the file’s current encoding. Save a copy with a different encoding like UTF-8, then try importing that new file into Power Query.
Only Some Rows or Columns Are Affected
Mixed encoding within a single file is rare but possible. This often happens when data from different sources is combined. The fix is to split the import process. You may need to clean the source CSV file outside of Excel or use advanced Power Query steps to treat specific sections with different encodings.
Text Encoding Options in Power Query: A Comparison
| Item | UTF-8 | Windows-1252 | ISO-8859-1 |
|---|---|---|---|
| Best For | International text, web data, modern applications | Text from Windows systems in Western Europe and the Americas | Older systems and software in Western Europe |
| Character Support | Extremely broad, supports most global scripts | Limited to Latin alphabet with some extra symbols | Similar to Windows-1252 but with slight differences |
| Common Garbled Pattern | Smart quotes or em dashes show as “ or — | Characters like é, ñ, or € show as é, ñ, or € | Similar garbled patterns to Windows-1252 |
| Default in Power Query | Often the default for new imports | May be the system default on some Windows setups | Not typically a default |
You can now import CSV files without garbled text by setting the correct file encoding. Use the File Origin dropdown in the import preview for the quickest fix. For existing queries, use the Transform > Using Locale feature. As an advanced tip, you can set the default encoding for all future CSV imports by modifying the regional settings in Windows Control Panel, which influences Power Query’s initial detection.