Forum Discussion

ahah95nY's avatar
ahah95nY
New Member
1 year ago

Files combining live export with pivot tables corrupting

We have a few files that produce reports using a combination of live exports from PowerBI and pivot tables built on top of the export data. We would prefer to build these reports in PowerBI, but our IT security framework doesn't permit us the right access to the data model to create new reports and metrics within PowerBI itself.

 

We've had the following corruption error with a couple of these files, but haven't been able to work out what's causing it:

"we found a problem in [file name]. Do you want us to recover as much as we can? If you trust the source of this workbook click yes".

 

Excel then repairs the workbook by hard coding the exported data and all pivot tables in the document, meaning that it has to be rebuilt.

 

Does anyone know what could be causing this and what we can do to avoid it in future? 

 

In case helpful to know, the file in question is 2.5mb in size, and is pulling around 7500 lines of data across four connections. There are 7 pivot tables sitting on top of the data.

Need Help

4 Replies

  • ahah95nY's avatar
    ahah95nY
    New Member
    1 year ago

    Hi

     

    Thanks for this. Unfortunately I don't have access to the data warehouse, so can't use powerquery before exporting.

     

    When this happens the workbook corrupts on opening, presumably because a piece of data changed overnight (we have daily refresh, pulling data from multiple forecasting systems open to 200+users). Is there a way I can use powerquery within the spreadsheet (rather than pre-import) to help with this?

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi ahah95nY 

      Yes, you can use Power Query directly within your spreadsheet to help with your data tasks! Here’s how you can do it:

      1. Open Excel and go to the Data tab.

      2. Click on Get Data to see a variety of data sources you can connect to, such as files, databases, and online services.

      3. Select the data source you want to connect to and follow the prompts to load the data into Power Query.

      4. Once the data is loaded, you can use the Power Query Editor to transform and clean your data. This includes filtering rows, removing columns, merging tables, and more.

      5. After making your transformations, click Close & Load to load the data back into your Excel worksheet.

       

       

       

       

       

       

      Best Regards,

      Jayleny

       

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

      • ahah95nY's avatar
        ahah95nY
        New Member
        1 year ago

        Hi there

         

        Thanks for this! Unfortunately the dataset isn't available as a datasource within PowerQuery using this method, presumably because I don't have access to the underlying data lake.

         

        It sounds like our only option is to either 1) convince the IT team (who do have access) to resolve this, or 2) negotiate access ourselves.