Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Power BI not exporting all rows from a table

I have this bug in multiple reports where when you export a table less than half of the rows are actually included in the excel file. it looks I'm not the only one who's experienced this. I see similar posts from years ago how is this not fixed yet??

Status: New
Comments
v-qiuyu-msft
Community Support

Hi @Mikerossanthony,

 

There are some limitations when exporting data from a visual, you can refer to this article

 

  • The maximum number of rows that can be exported from Power BI Desktop and Power BI service to .csv is 30,000.

  • The maximum number of rows that can be exported to .xlsx is 150,000.

  • When using DirectQuery, the maximum amount of data that can be exported is 16 MB. This may result in exporting less than the maximum number of rows, especially if there are many columns, data that is difficult to compress, and other factors that increase file size and decrease number of rows exported.

  • If the visual uses data from more than one data table, and no relationship exists for those tables in the data model, only data for the first table is exported.

In your scenario, please check if you hit one of the limitation. If not, please clarify "I have this bug in multiple reports where when you export a table less than half of the rows are actually included in the excel file". If possible, please share a issued report with us to reproduce the issue. 

 

Best Regards,
Qiuyun Yu 

Anonymous
Not applicable

Hi @v-qiuyu-msft,

I have a really small table (1321 rows) as a result of deduplicating source data in power query. I load it to the table in PowerBI Desktop (October 2019) without any aggregations (Don't summarize on every column selected) and I immediately see that there are few rows missing, why is that? There are no filters applied at any stage.

 

EDIT: I've found some weird **bleep** that is going on in PowerBI at the Visual level: If you want to have a table without any aggregation (so you want each and every row from your base data to be present in Table visual) and you will pick just several columns of your data to be presented (because you want just those) and it will return rows which look just the same (because they are duplicated or because something that is differentiating those rows is in column which is not taken to the table), even if you explicitly picked "Don't summarize" for each element in table - it will bring those rows which looks the same down to just one row.

 

2019_10_23_10_50_33_Window.jpg

TedChila
New Member

I'm getting the same symptom. Export from a table visualization does not contain all of the rows. Not a filter issue. All rows have an identity column included, so they are unique.

StacyP
New Member

I am also getting the same error.  I need all rows exported from my table visual, after the data has been transformed, so that I can incrementally grow my master dataset.  This feature does not work in Power BI using append ( https://community.powerbi.com/t5/Desktop/Using-append-to-grow-a-dataset/m-p/387305#M176370)

 

There must be a way to extract all transformed data from edit queries in Power BI, but so far I'm running into these roadblocks.  Does anyone know of an alternative solution?

manalla
Helper V

Hi, I am also facing similar issue. My Table contains 2185 rows, but when i export the table i see only 1468 rows getting exported. Why is remaining data not getting exported? Is this a known bug?

 

Thanks

Manoj

cggreeng
New Member

Add this as a new measure in your fields

 

Row_Number =
CALCULATE (
COUNTROWS(TableNameHere),
FILTER ( ALLSELECTED ( TableNameHere ), TableNameHere[mostuniquefieldhere] <= MAX ( TableNameHere[samemostuniquefieldhere]) )
)
Then add the Row_Number field to the Table.  This should add a unique Row Number to each row.  Then try exporting it.
bkeefe
Frequent Visitor

I'm experiencing this issue in August 2021.  No indication why it's happening.

Row count in Power BI report = 1687

Row count in exported Excel = 1577

 

It's also providing ~38,000 rows in Excel for a table of ~133,000 rows in Power BI with no indication why.

 

I see there is a warning about sampling, but shouldn't that only kick in when the dataset exceeds the limit indicated in the export options (i.e. 150,000 rows)?

scottdigs
Frequent Visitor

I had this same issue, but I realized what was happening in my case. The table I made in the Power BI visual had limited columns, and some rows appeared to be duplicates on the visual. The COUNT function was working correctly (i.e. counting the duplicates), but only unique records were being displayed in the table visual and data export.