New Excel file format has different navigation steps?

[dps]

Something changed with the Excel exports from our Oracle system, and IT does not understand.

Previously, the Excel.Workbook function returned five columns (Name, Data, Item, Kind, Hidden) and Navigation auto-filled = Source{[Item="sheet1",Kind="Sheet"]}[Data]

Now, Excel.Workbook returns only two columns (Name, Data) and Navigation auto-fills = Source{[Name="sheet1"]}[Data]

In a Dataflow, we use Sharepoint.Contents, then filter to the latest file, then Excel.Workbook to convert the binary.  It has worked for months, but now it says "DataFormat.Error: File contains corrupted data".  Making a small edit and saving the source file will fix the issue, which may offer a clue, but doing this manual step is impractical.

Power BI Desktop does not have the DataFormat.Error and works fine after changing the code to Source{[Item="sheet1",Kind="Sheet"]}[Data].  However, we need the Dataflow to work.  Does anyone have any insight?

[Anonymous]

Hi @dps ,

You can try the following steps to troubleshoot the issue:

• Check if the Excel file is corrupted. You can try to make a small edit and save the source file to see if it fixes the issue.
• Check if the file format has changed. You can refer to the documentation on how to troubleshoot connector and format issues in mapping data flows.
• Check if the numeric data is changed when imported from Oracle datasource. You can refer to the documentation on how to troubleshoot this issue.
• Check if the M query is causing the issue. You can refer to the documentation on how to troubleshoot drill through in Power BI Desktop and Power BI Service failing due to data refresh load.
• Check if the CSV files coming from SharePoint online Folder are causing the issue. You can refer to the documentation on how to troubleshoot refreshing CSV files coming from SharePoint online Folder.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[dps]

• Yes, making the small edit and saving will fix the issue.  However, we use daily Dataflow refreshes on many different daily exports.  So it is impractical to do this for every file every day.  Given that this manual step does allow the file to be read though - does that offer any insight into what the originating problem is?  What does that say about the xlsx file?  What could be happening in our Oracle financial system that causes it to export corrupted files?  
• The format is still xlsx, before and after this issue
• The data did not change, and the error happens during a step before analyzing individual fields/columns
• The M query still works with the older exported files, so that is not the issue
• It is not a CSV.  It is an xlsx file.