Forum Discussion

prathijp's avatar
prathijp
Helper I
11 months ago
Solved

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

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...
data engineering
  • anilgavhane's avatar
    anilgavhane
    11 months ago

    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.

Anonymous's avatar
Anonymous
Not applicable
11 months ago

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 .