Files combining live export with pivot tables corrupting

[ahah95nY]

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.

[ahah95nY]

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]

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]

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.

[Anonymous]

Hi @ahah95nY 

Please refer to the following post Solved: Excel Export File from a Table is Corrupt - Microsoft Fabric Community.

Can you check your data for any errors, and if there are any, please replace them in Power Query before importing the data.

Best Regards,

Jayleny

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