From PBI Desktop, how do you copy data in a Matrix visualization to be pasted into Excel?

[Funk-E-Guy]

I have my matrix set up so that it shows me 6 columns and ~1500 rows. But when I try to export, it tries to export the full dataset and tells me it's too large.

Due to an unrelated issue, I am currently unable to publish my model to our company workspace. Therefore, I cannot powerquery the model from Excel as a workaround at this time.

I understand that there's a feature that's been requested and confirmed in development which will allow exports in the same summarized format as the visual here. Until then, I seem to have no way of just ripping the data from the visual on PBI Desktop and into Excel. Unless I'm missing something.

Does anyone have any additional workarounds I can try?

[PC2790]

Hello  @Funk-E-Guy , 

I faced the same problem in my project and to fix that, I used a slicer with the Slider option as on. You can put any quantitave field in the slicer. For Example: I used volume sin there.

And then I figured out the optimum threshold after which the export breaks with the error.

It was generally 7000-8000 in my case.

After the extraction, I combined all the excels seprately for the required job.

I am not sure if it is an efficient way of doing it, but it worked for me.

[samdthompson]

@PC2790 wouldnt it be easier to analyze in excel via a data connection which saves any combinations and limits?

[PC2790]

Yes, could be.

I did not try it at that time. Would be worth trying provided that the report is published in power BI service. I think in this case, it is not. Hence I suggested the manual approach.

[Funk-E-Guy]

Due to an unrelated issue, I am currently unable to publish my model to our company workspace. Therefore, I cannot powerquery the model from Excel as a workaround at this time.

[samdthompson]

Hello, the summarized or not export bit is governed by an option in the PBIX file. Sounds like the author has limitied the export to summarized only. Three solutions would be:

1. Get the author to change the setting to: "Allow users to export both summarized and underlying data from the service or Report Server"

2. Access the report direct from Excel with Insert>>>PivotTable>>>FromPowerBI. You would need to set all the same filters etc as currently in your matrix

3. Export the data connection to excel via the Analyze in Excel function in powerBi service: Export>>>AnalyzeInExcel