Not Exporting All Rows (20,000)

[Thigs]

Hi all,

So I've never seen this particular issue before. I have a table visual in PBI desktop that I am trying to export to a CSV file. The table uses columns from two datasets that have a many-many relationship. There is a total of 10 columns and just over 19,000 rows. When I export it, however, I get about 11,000 rows.

I am not getting the message that my dataset is too large and may be sampled. I have tried filtering my data to break it up - it still happens even with very little data. I have tried removing columns, adding columns, etc. I have tried creating a third table as a key and matching it to the other two, creating a many-one and one-many relationship between the three. None of those are working. 

Has anyone had this issue? Any ideas on how to fix it? I can't export from the service as my company's IT department has limited that permission. 

[v-luwang-msft]

Hi @Thigs ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[v-luwang-msft · Accepted solution]

Hi @Thigs ，

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. 

And if question still not solved ,could you pls share your pbix file and remember to remove confidential data.

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[amitchandak]

@Thigs , Just wanted to check if the visual display 19K rows. Usually, power bi group the data, so line-level numbers do not match.

[Thigs]

Here's a screenshot - even if the data is being grouped, I figure the totals should match, and they don't. My total of Boxes and QTY don't match when I export, so that's part of the problem/how I know it's not working. Order number should be distinct, and Count of Order Number is a Distinct Count where everything is 1. The remaining columns are all dates. 

[Thigs]

Hi!

I think it is displaying all 19,000 rows. I created a distinct count column to count the orders (my key) and the total count is 19,000. Each row has a value of 1, so I think it should be showing all 19,000. Is there a way to double check?