cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GDSHAW
Advocate I
Advocate I

I want Export data of matrix visual in matrix form not in tabular form.

I am using matix visual, with fields ( Row = Member name, column = Date and value = Spent hours ) and i am getting the data  in matrix format perfectly (in power BI desktop/ services). But when I export the same to excel, I get it in tabular form. Is it possible to get the excel file in the same format as showing in power bi desktop/services( matrix form).

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

Hi @GDSHAW,

 

In Power BI, Export Data feature is used for exporting data, without keeping visual format in .CSV file currently. You can take a look at this article: Export data from Power BI visualizations.

 

To work around the issue temporarily, you can use @MattAllington's suggestion to create a PowerPivot table in Excel. Or you can use PowerBI Tiles add-in in the Excel to get matrix visual. See: Integrating Power BI Tiles into Office documents.

 

Besides, I found there is a same feature request posted in Power BI Idea forum, you can vote and comment on it: Allow Table and Matrix Data Exports to Match PowerBI Visual.

 

If you have any question, please feel free to ask.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

32 REPLIES 32
KarthikKV
Resolver I
Resolver I

The post was opened in 2016 and still the idea is not implemented and under planned status. Sometimes I feel microsoft is killing their own product 😞

Congldv
Regular Visitor

I hope that below steps can help you:

1. Export visual but name the extension ".txt" instead of "csv" as default

2. Open excel and import data "From Text", browse to the above file and customize some settings like headers, delimiter or width to define a table

3. Finish import the data to excel

PowerBiNinja84
Advocate I
Advocate I

To resolve this I did the following:

  1. Created Measures for each column in the Matrix visual. 
  2. Used a Table visual instead of Matrix.
  3. exported data from table

I made a demo video here:

https://www.youtube.com/watch?v=bpwkUpxjH-E&feature=youtu.be

 

datadad84
Advocate IV
Advocate IV

I was able to export my pretty matrix visual by doing the following:

1. Created a table visual.

2. Created measures using calculate and filter to match the columns that were in the original matrix table.

3. Exported Data using elipses in upper right of table visual.

Can you share your example with the link. Tried doing that but couldn't

check out this video for a workaround: https://www.youtube.com/watch?v=bpwkUpxjH-E&feature=youtu.be

aandi
Frequent Visitor

Hi all,

 

I wrote an in depth blog on this topic on my blog here: https://www.automationandinsight.com/index.php/2020/04/07/power-bi-export-woes/

 

Ultimately, this is a feature miss that Microsoft tried to stopgap with bolting on SSRS to Power BI in the form of paginated reports, and the workarounds are ugly and scary. It could be an opportunity to productize a hacky solution, but honestly I think that Microsoft needs to address this one, and I'll beat the drum for that.

There are many custom visuals that other companies have created. I honestly cannot believe that neither Microsoft nor any other company have created a version of the matrix visual that can export to Excel while retaining the row hierarchy levels and column groups, as SSRS does.

aandi
Frequent Visitor

Hi Eyles,

 

I explored this and mentioned it briefly on the 4 part blog I wrote and posted to this forum. This is by design because custom visualizations utilize the Javascript API and the Javascript API has this limitation. Microsoft has to choose to "limit break" this.

Jaydip_Dey
New Member

Hi,

The best feature of this workaround is the use of PowerBI Publisher.

Where you need to connect and the data model to the excel and make the report in excel.

 

For any other reference, please check how PowerBI Publisher works.

Deemah_EO
Advocate I
Advocate I

This feature should added to Power Bi , we need it soo much !!!!

Please add it to the next update !!!

Please add the proper export visual feature, currently, export matrix data into excel is giving detail data not in pivot format, because the client needs this for next level calculation.

Can anybody have any updates on this, I need really a download feature of the matrix in excel, right now it is not working.

The update is it doesn't work this way.  You can find plenty of suggestions for this at ideas.powerbi.com

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/35167033-matrix-data-export-to-ex...



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

The update is it doesn't work this way.  You can find plenty of suggestions for this at ideas.powerbi.com

 

>> At ideas.powerbi.com, everybody is asking about matrix export feature, did not have any suggestions at any blogs of your page, just found that everybody saying the same and no one has any update regarding the fixes.

I am at client side and we purchased 100+ licenses and this functionality is not available yet.

The client doesn't want to continue with PowerBI so, need these fixes otherwise we will move to another product.

 

@MattAllington : Please help me as soon as possible by fixing this matrix export issue. 

As i saw, idea voting is started for this topic by July 2016 and nobody giving us any date regarding this....

The way to get the software changed is by voting for ideas. When I want change, I use social media and seek support from the community. Maybe you should do that. 

 

Perosnally, I don’t see this as a major issue, but just an inconvenience. Microsoft is clearly building this product as a live, interactive tool that is designed not to need “export to Excel”.  If this is such a major requirement that 100 users can’t use the tool, then my advice is to revisit how your configuration of the solution is actually delivering value (or not). Why do they have to Export?  Have you tried Analyze in Excel?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

The way to get the software changed is by voting for ideas. When I want change, I use social media and seek support from the community. Maybe you should do that. 

>> 154 vote is already there by users and for my other clients I used Jaspersoft, Tableau and they are giving us this as a basic feature, so why Microsoft is not looking into it?

 

Perosnally, I don’t see this as a major issue, but just an inconvenience. Microsoft is clearly building this product as a live, interactive tool that is designed not to need “export to Excel”.  If this is such a major requirement that 100 users can’t use the tool, then my advice is to revisit how your configuration of the solution is actually delivering value (or not). Why do they have to Export?  Have you tried Analyze in Excel?

>> As per client, we need “export to Excel” as matrix download because clients need to share their matrix data to other users in excel and for cross-checking with manual excel sheets we need that type of data in excel otherwise how they can do?

 

The client has to prepare again a pivot in excel for PowerBI matrix report because PowerBi is not exporting matrix data. Its a rework for the client which we already did in powerBI report.

 

Have you tried Analyze in Excel?

>> It's again a rework for preparing a report.

 

So, please understand our pain and give us some fixing date.

If they need to export to Excel to do further analysis then maybe the Power BI report is not designed properly (just a guess).  Also, why not use Analyze in Excel?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

This is definately an issue.

I've created a PowerBI Matrix that 'pivots' textual data, rather than numerical.

To put it simply, I have a number (50K+) of suppliers, each supplier can have a number of services (10+) and their statuses.

I want the following and to be able to output the results to excel.  The combined total when tabular is 1.4 M rows, this is not workable.  Hence why the Matrix view is so important.  Note Service Status is a separate table with ServiceName and ServiceStatus.  ServiceName = ServiceA, ServiceB, ServiceC etc (col names) and ServiceStatus would become the value (FirstValue).

 

Supplier | Service A  | Service B | Service C ......   Service H

Acme Holdings | OK | INVALID | OK | CORRUPT

Bunny Ltd | OK | OK | OK | OK

Road Runner Plc  | INVALID | INVALID | CORRUPT

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors