Forum Discussion
Error with SharePoint OneDrive folder when switching from Power BI desktop to online
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?
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
5 Replies
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
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:08ZOk, 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
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
