cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Export underlying data

HI

I am exporting underlying data from my report(Matrix), and i am getting the correct count of rows, but all columns are not included in the excel data, i wants to export the data with all columns which are there in source table, is there any way to do this. Can anyone help me on this. Thanks in advance

 

Rajmohan 

2 ACCEPTED SOLUTIONS

Hi @Anonymous,

I got information that when using the “export underlying data” option in Power BI Service, the export file will contain columns which are used to create the visual together with all “Text” type columns. If you want to export all columns, you would need to set the rest columns to be “Text” type  or include the rest columns in your Matrix visual.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
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

Hi,

 

I'm also having the same problem and from my side the issue is that I'm using a calculated measure and I'm trying to find some option to solve this but still no luck 😞

View solution in original post

21 REPLIES 21
vinitshah
New Member

Hello, 

 

I have a power bi dahsboard with multiple visuals. 

Issue: When I try to export the data for a specific visual in summarzed or underlying data it does allows me to do both but where in some visuals gives me the option to export only in summarized format and the underlying data is greyd out. I also checked the options and settings where the it is enabled fo users to export data in both format.

Please let me know if you need more light on the issue.

Thanks in advance.

Anonymous
Not applicable

First we have to enable the option in PowerBI desktop to download the underlying data and it can do only 150,000 rows.

 

https://www.youtube.com/watch?v=p2NN0Mxputc

 

Just create a summarized table and from there you can easily download the data with or without filters.

 

Thanks,

Karthik

 

Anonymous
Not applicable

Try to change the datatype of the measures and lets try it, you'll get all the data

Anonymous
Not applicable

HI All,

 

After I publish the dataset to Premimum workspace. When I create a report with Table visual on Top of dataset withrequired fields.

When i try to export the visual I see the in the excel output saying it has exceeded the limit count of 150000 rows but where it actually doesnt  have that many rows.

 

Any tips why Excel is showing wrong count of data. when exported to excel. Any chnages need to do in Options.

 

Thanks in advance. 

@Anonymous Which columns are missing? Are they measures or calculated columns? Is there a threshold, such as, you have 20 columns and only the first 18 are showing -> or something to that effect?


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Anonymous
Not applicable

Hi

Those columns are not calcualted columns, like we have a employee table, we applied filters like location,billing status etc.,while we are exporting data, some columns like DOJ,Ageing(these are not calculated columns) missing,and there is no threshold out of 47 columns i am getting 33 columns, that to those are not in order

Hi @Anonymous,

Do you take the operation that export data in Power BI Service or in Power BI Desktop? If you export data in Power BI Service, please help to post screenshots about your scenario. Also try to create Matrix visual in Power BI Desktop and check the export result.

If you are using Power BI Desktop, would you mind sharing me the PBIX file with which I can  test?

 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-yuezhe-msft

I am taking export operation from power bi service after publish the report from desktop, i have created matrix in desktop then i exported the data it is giving only summarised data, from power bi service if i export the data with underlying data option it is exporting data to excel file with 33 columns only, and i have around 50 columns in the table which i used for the report, my requirement is i need all the columns to be exported in the excel file from power bi service while exporting data, i am attaching screenshots of power bi service, desktop and excel file. Please check and help me out on this.export data from serviceexport data from serviceexported file column count is 33exported file column count is 33desktop in the right you can find the table with around 50 columnsdesktop in the right you can find the table with around 50 columns

 

Thank you

Rajmohan

Hi @Anonymous,

I got information that when using the “export underlying data” option in Power BI Service, the export file will contain columns which are used to create the visual together with all “Text” type columns. If you want to export all columns, you would need to set the rest columns to be “Text” type  or include the rest columns in your Matrix visual.

Thanks,
Lydia Zhang

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

@v-yuezhe-msft 

Hi, When I am exporting underlying data then only 1 row i.e 'Total' row of the Matrix is getting exported but not the whole data. Can you tell me why it is happenning?

Hello Lydia 

I have kind of the opposite problem; I do not want users to export the underlying data from my tables/matrices. 

 

Is there anyway to disable that option and allow exporting only summarised data?

 

Thanks 

Panos

Hello Lydia 

I have kind of the opposite problem; I do not want users to export the underlying data from my tables/matrices. 

 

Is there anyway to disable that option and allow exporting only summarised data?

 

Thanks 

Panos

Anonymous
Not applicable

@v-yuezhe-msft Thank you for this valuable information! I have left a feedback ticket for the documentation which is lackign this information. It's important to understand when reviewing security!

I am missing two of the three value columns that are present in the visual during export.  The other visuals using the same tables are providing all the value columns during export.

 


Seems odd to me that the visual showing all the value columns is only exporting one, but the visuals only showing one value is exporting all of them.

Hello,

 

I am try to export the data from Detail report in Power BI Services ," Underlying data" option grade out for one report rest of the report was able to export the data into excel.

 

Why only one report having an issue?

I noticed that when we export data from details report .  other columns exporting into spreadsheet which are not part of the detail report

  

Export data_PowerBI.JPG

 

 

 

 

Hi

 

I am struggling with the same issue.

 

export to underlying data is grayed out and error message was that i dont have permissions.

 

is this something to do with Admin permissions?

 

 

@giri41 Sounds like it. Power BI tenant settings allow you to disable exporting data, you would need to speak with your Power BI or Office 365 admin to enable this for you if you needed it.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Hi,

 

the information in the link below helped me to change from Measure to column and then I'm able to export data using the underlying option.

 

Hope that it helps you

 

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Hi,

 

I'm also having the same problem and from my side the issue is that I'm using a calculated measure and I'm trying to find some option to solve this but still no luck 😞

Hi @Anonymous,

Based on your screenshot, I note that you apply filters to your Matrix visual, and it seems that all underlying data has been exported in Excel, but the data is displayed in different format as that in Matrix visual.  Would you mind sharing me the original sample file or PBIX file?

Thanks,
Lydia Zhang

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

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors