Forum Discussion

prathijp's avatar
prathijp
Helper I
11 months ago
Solved

Dataflow Gen2 Misreads Excel File – Encoding or Format Issue

I’m 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.

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

data engineering
  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    11 months ago

    Hi prathijp ,
    Thanks for reaching out to the Microsoft fabric community forum.

    In Power Query, use the Excel.Workbook([Content]) function. After connecting through the SharePoint Folder, locate the file’s binary content and apply this function to correctly decode the workbook structure. This ensures the Excel file is interpreted and loaded properly.

    I hope this information helps. Please do let us know if you have any further queries.
    Thank you

5 Replies

  • Shahid12523's avatar
    Shahid12523
    Community Champion
    11 months ago

    Problem: Dataflow Gen2 misreads Excel from SharePoint due to encoding/streaming issues.
    CSV works because it's plain text; Excel needs proper binary stream.


    Fix: Use Web.Contents() with a direct SharePoint download URL to fetch the actual Excel file:


    let
    Source = Web.Contents("https://...download.aspx?...YourFile.xlsx"),
    ExcelData = Excel.Workbook(Source, null, true)
    in
    ExcelData

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    11 months ago

    Hi prathijp ,
    Thanks for reaching out to the Microsoft fabric community forum.

    In Power Query, use the Excel.Workbook([Content]) function. After connecting through the SharePoint Folder, locate the file’s binary content and apply this function to correctly decode the workbook structure. This ensures the Excel file is interpreted and loaded properly.

    I hope this information helps. Please do let us know if you have any further queries.
    Thank you

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    11 months ago

    Hi prathijp 

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.


    Thank you.

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    11 months ago

    Hi prathijp 

    May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.


    Thank you

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    11 months ago

    Hi prathijp ,

    As we haven’t heard back from you, we wanted to kindly follow up to check if the suggestions  provided by the community members for the issue worked. Please feel free to contact us if you have any further questions.

     

    Thanks and regards