Exporting data is not showing all the rows

[syasmin25]

Hello, 

I am trying to export data from Power BI, however my export does not contain all the row level for the department that I am trying to. My Data in Power BI shows 1976 rows for the department whereas, my export has 1836 rows. Also, I tried to search for specific product purchases for that department to see where my row count is missing. And one of them had 50 rows difference. However, when I went through checkin gif all the IDs were there, it actually was. I guess I am a little confused. Has anyone else come across issues such as this?

[BShirer4]

I have found something interesting with exporting data from grids that might be relevant.  Many answers will point to missing data due to record count limitations of exports or errors in your transformations when loading data, but I have seen something else.

Lets say my data is showing sales qty data for a region, territory and location.... the data might look like this:
Region     Territory  Location              Qty
North       PA            Philly                  100  

North       PA            Philly                  100

North       PA            Pittsburgh          150

North       PA            Pittsburgh          200

The data above is coming from different, related tables, and when it is dispalyed in a grid, even if I do not choose to sum the Qty field, it only displays 1 row, but does sum up the rows that have completely duplicated data, and looks like this:

Region     Territory  Location              Qty
North       PA            Philly                  200

North       PA            Pittsburgh          150

North       PA            Pittsburgh          200

But when I export the data to a CSV file, the data looks like this:

Region     Territory  Location              Qty
North       PA            Philly                  100

North       PA            Pittsburgh          150

North       PA            Pittsburgh          200

So as it stands, the grid is rolling up the numbers, but when it is exporting, it only exports 1 row for each duplicate.   I think this is a bug in the grid export feature, and the work around is to add another column to the grid that would force those duplicated/rolled-up rows to be unique an not rolled up.

[mnchica]

I just found the exact same issue.  In my scenario, there are legitimate duplicate rows, so my solution was to add an Index column to the dataset, forcing uniqueness.  This fixed my issue.

[Bikash7787]

I had same problem. Please check your transformation there might be duplicate data. Remove the duplicate and you will get the correct number on dashboard as well as on report. 

[v-diye-msft · Accepted solution]

Hi @syasmin25 

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. 

[AlessandroP]

I had tried all of the helpful comments but I was not able to see all the data in either excel or csv, about 200 rows with all connections set to import.

In my case, what worked was to add a row number to the table connection and adding the column to the visual table. Forcing the visual table to show the row number for all records. I was able to add it in the back-end using sql (i.e. ROW_NUMBER() over(order by [Column])). Just wanted to add it to the comments in case someone finds this useful.

Cheers

[az38]

@syasmin25 

I think you have about 60 duplicated rows in your data

you can try to find it by setting Do not aggregate in fields settings in Vizualization pane or using data view in Data model left tab

[syasmin25]

I have manually counted the rows that are exporting with the table visual in power bi and it is both 28 rows. However, PowerBI says that it is 35 rows even though it was showing 28 rows.

[syasmin25]

I have made sure that none of my tables are aggregating the data. It still does not match the row count for the department.