MS Fabric Data Flow Gen 2:Data encoding issues while ingestion data from excel in SharePoint folder

[prathijp]

I am trying to read and transform an Excel file stored in a SharePoint folder into a Lakehouse table using Dataflow Gen2 in Microsoft Fabric.

I’m using the SharePoint Folder connector, and while the connection works, the Excel data is being read with incorrect or garbled characters, which suggests a possible encoding or format issue.

Interestingly, when I try the same process with a CSV version of the file, the data is read correctly by the dataflow.

I’ve already ensured the file is saved as a proper Excel Workbook (.xlsx), but the issue persists. I’ve attached a sample of the data being read incorrectly. Also I tried updaing the encoding to UTF-8, which also did not help. I tried chaning the locale as well but no luck.

Could you help me understand why this is happening and how to resolve it?

[v-tsaipranay]

Hi @prathijp ,

Thank you for reaching out to the Microsoft fabric community forum. 

The issue you're facing, garbled characters when reading Excel files from a SharePoint folder using Dataflow Gen2 is a known behavior caused by how the SharePoint Folder connector handles Excel files.

It treats them as binary streams, which can lead to misinterpretation of character encoding unless explicitly decoded. You've already confirmed that the CSV version of the file works correctly, which rules out a content or locale issue and points to a decoding problem specific to Excel.

The most effective resolution is to apply the Excel.Workbook([Content]) function in Power Query after connecting via the SharePoint Folder connector as mentioned by @Shahid12523 . This explicitly decodes the binary content into a readable Excel format.

Alternatively, using the Excel connector directly (instead of SharePoint Folder) avoids this issue entirely, as it natively understands Excel file structures. Also, ensure the Excel file is saved as a clean .xlsx file without macros or hidden objects, and set the appropriate locale in Power Query to handle special characters correctly. For reference: Set a locale or region for data (Power Query) - Microsoft Support These steps should resolve the encoding issue without relying on CSV conversion, which you've already tested successfully.

Hope this helps. Please feel free to rech out for any further questions.

Thank you .

[anilgavhane · Accepted solution]

When you use the SharePoint Folder connector, Power BI and Fabric treat the Excel file as a binary object. If the binary isn't properly decoded using the right method (like Excel.Workbook([Content])), the data can appear garbled or misencoded, especially for non-ASCII characters or formatted cells.

CSV files don’t have this issue because they’re plain text and don’t require binary decoding which is why your CSV version works fine.

Recommended Fixess

1. Use Excel.Workbook([Content]) in Power Query

• After connecting via SharePoint Folder, navigate to the file’s binary content.
• Apply the Excel.Workbook([Content]) function to decode it properly.
• This step is crucial to interpret the Excel structure correctly.

2. Avoid Macros or Hidden Objects

• Make sure your .xlsx file is clean — no macros, hidden sheets, or embedded objects.
• These can interfere with decoding and cause character corruption.

3. Set Locale Explicitly

• In Power Query, go to File > Options > Regional Settings and set the locale to match your data (e.g., English (India) or UTF-8 compatible locale).
• This helps with character encoding and date formats.

4. Try the Excel Connector Instead

• If possible, use the Excel connector directly instead of SharePoint Folder.
• This bypasses the binary decoding issue and reads the file more reliably.

5. Convert to CSV as a Last Resort

• If none of the above work, automate conversion to CSV using Power Automate or manual export.
• CSVs ingest cleanly and avoid encoding pitfalls.

[Shahid12523]

Issue: Excel files from SharePoint via Dataflow Gen2 show garbled characters due to binary misinterpretation.
✅ Fixes:
- Use the Excel connector directly instead of SharePoint Folder.
- If sticking with SharePoint Folder, decode using Excel.Workbook([Content]) in Power Query.
- Ensure the file is clean .xlsx, no macros or hidden objects.
- Set locale explicitly in Power Query for proper character handling.
🛠️ Workaround: Convert Excel to CSV (via Power Automate or manually) — CSVs ingest cleanly.