Error When Exporting Matrix Table in Excel

[newpbiuser01]

Hello,

I have a matrix table that loads without any errors in desktop and also when I load it in Power BI Service. However, when we try to export the table in Excel, I get an error saying there is a problem with some content. 

I have checked and there are no errors in the measure. The measures being used in the matrix visual all have null handling logic in in. We didn't have this error, but started seeing this recently (with no changes in the dashboard)

Would anyone know why we are seeing this error? Really appreciate any help. 

Thank you!

[v-tsaipranay]

Hi @newpbiuser01, 

Thanks for reaching out to the Microsoft fabric community forum.

As you mentioned in the post you are taking matrix table from the dashboard there are some limitations in dashboard exporting data.

•  So, kindly try exporting the data to a .csv file. If you filtered the visualization, then the .csv export is filtered as well. Your browser prompts you to save or open the file. By default, your export is saved to your local Downloads folder and open the .csv file in Excel.

For detail information please refer to the below link:

https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-export-data?tabs=powerbi-s...

Also, kindly review the data model for any unexpected data types or formats, particularly in your measures. Due to limitations in Microsoft documentation, you may encounter that error, so please refer to the image below.

Here are few similar example threads, please go through the links to get a better understanding.

https://community.fabric.microsoft.com/t5/Desktop/Exporting-a-Matrix-visual-to-Excel/td-p/1643080

https://community.fabric.microsoft.com/t5/Report-Server/The-matrix-cannot-export-the-current-layout/...

I hope my suggestions give you good ideas, if you need any further assistance, feel free to reach out.

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

Thank you. 

[newpbiuser01]

Hi @v-tsaipranay 

Unfortunately, I can't export to .csv when I try to export the data. It's defaulted and set to "xlsx". I can't change it to csv. Is there a way we can change that? 

Thank you!

[v-tsaipranay]

Hi  @newpbiuser01,

After creating your dashboard, you will find a three-dot menu. Within this menu, there is an option labelled "Export to CSV," which allows you to export your matrix table.

To provide clarity, I am including a screenshot of the location from which we need to export the data.

As demonstrated in the image above, click on the 'Continue' option, and the data will be downloaded to your local system.

I hope my suggestions give you good ideas, if you need any further assistance, feel free to reach out.

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

Thank you. 

[newpbiuser01]

Hi @v-tsaipranay 

I don't get that option, I only see Export Data which then brings me to "Which data do you want to export" where the file option is set to "xlsx" and is greye out. 

[v-tsaipranay]

Hello @newpbiuser01 ,

For export from matrix visuals using Data with current layout, consider the following:

• Matrices with columns and/or values but no rows will be exported as having rows and/or values but no columns

• Matrices with only one row and/or values but no columns will be exported as table (no right border separator)

• If the Switch values to rows formatting option is set to 'On' in Power BI for a table or matrix visual, the visual format would not be preserved when data is exported to Excel

• If the Row subtotals toggle is set to 'Off' in Power BI Desktop for a matrix visual, but the matrix visual has expanded and collapsed sections, exported data will contain subtotals for rows. To work around this issue, use the Expand | All command from the visual's context menu.

For more you may refer to :Export data from a Power BI visualization - Power BI | Microsoft Learn

I hope my suggestions give you good ideas, if you need any further assistance, feel free to reach out.

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

Thank you. 

[newpbiuser01]

Hi @v-tsaipranay ,

But this still wouldn't help me export the data in .csv so I don't get the error while exporting in Excel. How would I do that?

[v-tsaipranay]

Hello @newpbiuser01 ,

As I mentioned in my previous response, there are limitations when exporting the matrix visual to Excel. Therefore, exporting it to CSV might be the best approach for you.

Could you please include a screenshot of the error you encounter when exporting it to CSV?

Thank you.

[newpbiuser01]

Hi @v-tsaipranay,

I can't export to csv, that's the problem. The file format is greyed out and locked. I can't change it from Excel. 

This is also an issue if I'm exporting a table visual with more than the 150,000 row maximum. How can I change this setting to be able to export to csv? 

[v-tsaipranay]

Hello @newpbiuser01 ,

Thank you for providing detailed information, and I apologize for the late response.

To clarify, as you mentioned in your initial query, you referred to the dashboard. I have already explained that there are limitations when exporting a matrix visual to Excel from the dashboard, which is why I recommended exporting it to CSV instead.

From the screenshot you provided, it appears that you are exporting data directly from the report rather than the dashboard. Please try exporting the same matrix visual from the dashboard, where you will see the option to export to CSV.

So kindly look into the consideration in the following documentation:

https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-export-data?tabs=powerbi-s...

Regarding your second query, exporting a table visual with more than 150,000 rows may not be feasible. According to Microsoft documentation, the maximum number of rows that can be exported to an .xlsx file is 150,000. However, the actual limit may be lower based on query limits and visual types.

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

Thank you. 

[v-tsaipranay]

Hi @newpbiuser01 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[newpbiuser01]

Hi @v-tsaipranay ,

I read the documentation, and I'm afraid I dont think the issue is resolved. I had this report working earlier and started throwing the error a couple of weeks back.  I have created a dummy file with a much smaller data set to see if its the data size or the format, same issue. Would you know if a new update was deployed that might cause this issue?

The export shouldn't throw an error when a user tries to export it. I think it is acceptable if the formatting doesn't come through, but there shouldn't be an error. Is there no way to en-able the export to csv since we already know the .xlsx format isn't working?

Thank you.

[v-tsaipranay]

Hello @newpbiuser01 ,

Thank you for your update and for clarifying. Since you have confirmed that you followed the steps in the documentation and are still encountering the issue, I understand your continued frustration. It is concerning that the export functionality has stopped working correctly, especially after it had been functioning properly before. Here are a few things we should consider:

• To clarify, CSV export is only available from the dashboard's visualization menu (three dots "..."). It cannot be done directly from a report. Sorry for any confusion. Please confirm you are in the dashboard view when looking for the CSV option.
• If your report contains multiple pages and you are pinning visuals to your dashboard, please ensure to pin the visual with the matrix to the dashboard and then export the data from there.
• ⁠It’s possible that a recent Power BI update may have affected the export functionality. Check the Power BI Release Notes to see if any changes were introduced in the last few weeks that might impact exports.
• Although you have created a dummy dataset, specific data characteristics might still cause the error. Please ensure there are no hidden characters, unusual formatting, or excessively long strings in the data used for the matrix. Regarding the concern, I implemented it, and it is working fine for me.
• Explore Alternative Solutions: If exporting directly from the dashboard continues to be problematic, you could consider using the "Analyze in Excel" feature. This function works differently and might help you export the data without encountering the same issue.

Let me know if clarifying the location of the CSV export option resolves the issue, If I misunderstand your needs or you still have problems on it, please feel free to let us know. 

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thankyou.

[v-tsaipranay]

Hi @newpbiuser01 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-tsaipranay]

Hi @newpbiuser01 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[newpbiuser01]

Hi @v-tsaipranay 

As I mentioned earlier, no it hasn't unfortunately been resolved. I have tried the recommendations - even tried with a sample and much smaller data size, and same error. We did not have this error before. 

You mentioned that I should try exporting to csv, but that apparently isn't possible because that option is greyed out.

I have also raised it as an issue which is still being investigated: 

Exporting Matrix Table with Dynamic Formatting Thr... - Microsoft Fabric Community

[v-tsaipranay]

Hello @newpbiuser01 ,

I apologize for the inconvenience. Could you please confirm if you are using the latest version? Outdated versions can sometimes cause issues. Please install the latest version of PBI, and once updated, try republishing and exporting again through the dashboard. This should resolve the issue.

If you are using the latest version and still encountering the error, I recommend raising a support ticket using the following link:

https://learn.microsoft.com/en-us/power-bi/support/create-support-ticket

Thank you.

[v-tsaipranay]

Hello @newpbiuser01 ,

I wanted to follow up on our previous conversation. Has your issue been resolved or were you able to raise a support ticket with Microsoft Fabric Support?

Your feedback would be greatly appreciated, as it can help us better understand and address similar issues in the community.

Thankyou.

[newpbiuser01]

Hello,

As I mentioned  in my comment earlier, I have raised it as an issue which is still being investigated: 

Exporting Matrix Table with Dynamic Formatting Thr... - Microsoft Fabric Community

Unfortunately its not been resolved yet 😞

[FarhanJeelani]

Hi @newpbiuser01 ,

The error message you're encountering in Excel—"We found a problem with some content in data.xlsx"—typically indicates that something in the exported file is incompatible or corrupt. Since this issue occurs when exporting a matrix table from Power BI, here are some possible causes and troubleshooting steps:

Possible Causes:

1. Special Characters or Formatting:

   • Certain special characters (like control characters or invisible spaces) in column names, measure outputs, or data fields might cause Excel to flag errors.

2. Exceeding Export Limitations:

   • Power BI has export limitations, such as row or column counts. If your matrix visual exceeds these limits, the export file may become corrupted.

3. Null or Unexpected Values:

   • While you've implemented null handling logic, unexpected or unsupported data (e.g., extremely long text, overly large numbers, or unsupported formats) may still cause issues.

4. Recent Power BI Service Updates:

   • Changes or updates to Power BI Service might have inadvertently introduced compatibility issues with the Excel export functionality.

Troubleshooting Steps:

1. Simplify the Matrix Table:

   • Reduce the number of rows, columns, and measures in the matrix visual and test the export. This will help identify if the issue is related to size or complexity.

2. Check for Special Characters:

   • Inspect the column and row headers, as well as data values, for any special characters or non-printable text.
   • Test replacing special characters with standard ones or removing them entirely.

3. Verify the Data:

   • Create a simplified table visual with only the core data (no measures) from the matrix. Export it to Excel to see if the issue persists.
   • If the simplified export works, reintroduce measures or calculated columns one by one to identify the problematic element.

4. Test with Another Browser:

   • If you're exporting the matrix from Power BI Service, try using a different browser to rule out browser-specific issues.

5. Export to CSV:

   • Instead of exporting to Excel, export the matrix as a CSV file. Open the CSV in Excel to check if the content is being exported correctly.

6. Review Power BI Service Settings:

   • Ensure that no changes have been made to export settings in the Power BI Service. For example, export permissions or formats may have been modified.

7. Check for Power BI Service Updates:

   • Confirm whether any recent updates to Power BI Service are causing this issue by reviewing the Power BI Support page or forums.

8. Open the File with Repair:

   • When Excel prompts you to repair the file, click "Yes" to identify if Excel can fix the corruption and point out specific problematic content.

9. Contact Microsoft Support:

   • If none of the above steps resolve the issue, consider reaching out to Microsoft Support with the exported file for further analysis.

Let me know how these steps work for you!

Please mark this as solution if it heps you. Appreciate Kudos.