PBI Desktop not exporting all rows to Excel

[DeBIe]

Hello!

I would like your help for the following:

I have a table with 12000+ records in PBI desktop. When I export it to a .csv file, it will only give me around 1000 rounds. 

I have no clue why this is happening. Does anybody know what is going on?

Thanks for your help.

[DeBIe · Accepted solution]

@v-lionel-msft I have found the cause of my export problem. It seems that when I remove one column, I will get the data that I need. The column that was causing this issue came from the second table: 
(HasBookings = IF([Administration&Code Count]<>0,"Yes","No")

I use a normal table as visual. I don't understand yet why it is not working, but somehow it does now.

[Anonymous]

Power BI tools has in-built summarized (Aggrigative Data Level) behaviour, Which means suppose in our data if there are any dupilcates or repetative values it will comprise the data itself based on the data comparision 

As summed up by pbi, export count for Summerzied 30K & Underlying 150K is not true at all, there will be some differences while exporting the data

If you want to see the behaviour of summeraize then take 3-4 columns of data example with duplicates and try it 

PS: Datatypes may be also a reason for this 

[v-lionel-msft]

Hi @DeBIe ,

The data you export may be the data after being aggregated.

Export the data that was used to create a visualization 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ibarrau]

Hi. Exporting data can be tricky because there are some limitations and considerations. 

It's not the same exporting from Power Bi Service than Power Bi Desktop.

- In Power BI Desktop, you'll only have the option to export summarized data as a .csv file with a limit of 30k rows. The export will summarize your data. That is why you have less rows than the real data.

- In Power Bi Service you have two options to export. One is summarized data and the other is underlying data. Both of them can be in xlsx or csv. The rowlimit in this case is 150K.

Hope this helps.

Regards,

[DeBIe]

Hello @ibarrau ,

The reason I am using Power BI desktop for export is that my data is from two different tables. PBI Service won't let me export data from two different tables in one visual. Besides that, I am only getting around 1000 records so I am not even close to the limitation of 30.000 records. Any other ideas?

[v-lionel-msft]

Hi @DeBIe ,

You first count how many rows of data in the table visual, you can create a measure to count the rows.

"The reason I am using Power BI desktop for export is that my data is from two different tables."

Perhaps the data you exported is the inner join data of the two tables.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DeBIe · Accepted solution]

@v-lionel-msft I have found the cause of my export problem. It seems that when I remove one column, I will get the data that I need. The column that was causing this issue came from the second table: 
(HasBookings = IF([Administration&Code Count]<>0,"Yes","No")

I use a normal table as visual. I don't understand yet why it is not working, but somehow it does now.

[v-lionel-msft · Accepted solution]

Hi @DeBIe ,

Will it be this reason?

If not, we can only judge the reason by seeing your data model.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DeBIe]

That's probably the problem. Is there a way to rewrite this measure?

[DeBIe]

It works now. Actually what I did was remove the calculated column and rewrite it as a measure. Now it seems to work. How come?