Error with SharePoint OneDrive folder when switching from Power BI desktop to online

[TallyReportGuy]

Hi!

I have some M code that gets a list of Excel files from a SharePoint OneDrive folder like this:

Source = SharePoint.Files("https://company-my.sharepoint.com/personal/myname_company_com", [ApiVersion = 15])

The code runs fine when I run it through Power BI Desktop but when I publish it online I consistently get this error:

Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"File contains corrupted data."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataFormat.Error"}}],"exceptionCulprit":1}}} Table: XACT Claim Status.
Cluster URI: WABI-US-EAST-A-PRIMARY-redirect.analysis.windows.net
Activity ID: df4e156d-2e5d-4a2d-9b2c-81dae3a36642
Request ID: fd3a63dc-a033-e484-b258-810e6190e7b2
Time: 2022-12-16 14:42:27Z

After some more experimentation, it looks like the error occurs during the process of converting the binary data to rows and columns. Specifically, the M function that gets autogenerated:

= (Parameter1) => let
Source = Excel.Workbook(Parameter1, null, true),
Assignments1 = Source{[Name="Assignments"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Assignments1, [PromoteAllScalars=true])
in
#"Promoted Headers"

Has anyone else run into this?  Any idea why it might be different between the two environments?

[TallyReportGuy]

So I made that change and 2 things happened:

1. The refresh now takes about 10 minutes rather than 10 seconds

2. I get a new error:

Data source error: DataFormat.Error: <ccon>File contains corrupted data.. </ccon>;File contains corrupted data.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. Table: Query1.
Cluster URI: WABI-US-EAST-A-PRIMARY-redirect.analysis.windows.net
Activity ID: fc3e2b31-8e4b-4c33-85b0-fe96b981c3cd
Request ID: 913e3c09-8f7d-582f-297a-97c25c001b56
Time: 2022-12-16 16:43:08Z

[BA_Pete · Accepted solution]

Ok, well it's the same error but on a different query (Query1) this time.

My first guess would be that PQ is picking up a parameter file that's of a different format to the other files in the query. Do you have mixed file types in your source folder and, if you do, what measures have you already taken within the query to exclude these from both the sample file parameter and the transform & combine query?

Pete

[TallyReportGuy]

Sorry, the Query name has changed because I isolated the issue in a new PBIX file.  The folder only has Excel files and I filter on the extension just to make sure.

In order to eliminate the sample file as a potential issue, I removed it from Power Query and changed the function that gets generated to not need it:

= (Parameter1) => let
Source = Excel.Workbook(Parameter1, true, true),
Assignments1 = Source{[Name="Assignments"]}[Data]
in
Assignments1

[BA_Pete]

It looks like this error gets thrown by PBI for a number of reasons, with a number of different solutions.

I'd try the following, in order of severity, per the thread linked below:

1) Inspect each of the Excel files from the File menu within each file in case of any XML errors etc.

2) Also ensure that none of the Excel files are password protected.

3) Close PBI Desktop, delete cache files and restart.

4) Uninstall PBI Desktop, delete cache files and reinstall.

https://community.powerbi.com/t5/Desktop/File-contains-corrupted-data/m-p/548620 

Pete

[BA_Pete]

Hi @TallyReportGuy ,

It looks like a gateway error. Are you pushing this query through your enterprise gateway once it's published to the Service? If you are, you don't need to, so remove this source from your gateway and switch off the 'Use Gateway' switch in dataset settings:

Pete