Forum Discussion

TallyReportGuy's avatar
TallyReportGuy
Advocate I
3 years ago
Solved

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", [Ap...
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

     

    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's avatar
TallyReportGuy
Advocate I
3 years ago

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's avatar
    BA_Pete
    Super User
    3 years ago

     

    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's avatar
      TallyReportGuy
      Advocate I
      3 years ago

      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's avatar
        BA_Pete
        Super User
        3 years ago

         

        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