Forum Discussion

prathijp's avatar
prathijp
Icon for Helper I rankHelper I
1 year 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 ...
data engineering
  • Anonymous's avatar
    Anonymous
    1 year 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

Shahid12523's avatar
Shahid12523
Icon for Community Champion rankCommunity Champion
1 year 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