Can not export full data of table [very small dataset; couple hundred rows]

[jlie]

Hey everyone,

if have a strange problem, that first occured to me since the August update a couple of days ago. In some cases (was not able to specifically replicate the cause, but it seems to happen when my table contains Measures as table columns) my PBIX Files only export sort of "sample data", when I click the three dots at the top-right of an (table) visual and select "export data".

For example, I generate a table with 600 rows and pull a couple of static columns (either loaded from data source or calculated columns) into the table --> it exports all the 600 rows.

I pull some dynamically calculated columns (measures) into the table visual --> it only exports less than 100 rows.

Does anybody know where this comes from?

Did I miss a setting?
Is this a known bug?

Thank you for helping!

[v-huizhn-msft]

Hi @jlie,

Please double check the rows of table visual which you export less than data source. I think the measure summarized the table visual, so your exported data is less than original table visual. For example, I have a sample table below.

 

I create a table visual with static columns, I export data into csv file, and get the full data.

Table visual with static columnsexport data into .csv file
While I create a measure using the formula : Measure = COUNTA(Append1[Active]). When I create a table visual, it will summarize the table visual by the columns except the measure. So there are three rows in the table, it only export data to three rows as the table visual shows, less than original table.

Table visual summarized by Active    

Export data into .csv file, less than source table
Please check your scenraio is similar with mine, please feel free to ask any question if you have.

Best Regards,
Angelia

[jlie]

@v-huizhn-msft Thank you for answering, but unfortunately I cant follow you there. Both examples export exactly what your table visual shows. Thats exactly what I need, and that is the exact opposite of what happens in my case: My export does not contain the rows of the table visual.

Let me clarify: I have a table in my data model that contains 600 rows. I create a table visual that shows some of the columns (not all of them) with no filter, e.g. showing all the rows of that table, that are in my data model. I export from the table visual and my export contains only 100-or-so rows.

Its not about aggregating measures or "show rows with no data". Sorry for not beeing more specific in my opening post, I was not aware that it might be misunderstood. I hope, this explanaition is better now.

[v-huizhn-msft]

Hi @jlie,

Please update your desktop to latest version and check if it works fine. And check if your sample table has duplicate rows? If there are duplicate rows in your resource table, when you add it into table visual, it will remove them just leave one row automatically. Please add a index column into your table, and show it in table visual. All the duplicate rows will display in index column.

Best Regards,
Angelia

[Anonymous]

Thank you Angelia, I was having the same problem, and adding an index column fixed it.

[jpt1228]

This was especially helpful. I could not figure out why my visual would show 1,365 and when I exported the data there were only 1,150 rows in excel. To correct this I added a merged column across several columns to make a unique column. Then I remove duplicates. Problem solved.

[Anonymous]

Hi @v-huizhn-msft,

I've an issue exporting, sometimes the data that I export on the desktop version don't match with the online version exportation.

This is obvious when we have a lot of data/rows to export.

I think the issue can be the method that desktop use to export the data to csv. The desktop version export rounding the values and the online version not.

In this example we have the same report/data with a SUM. In the left Online version, on the right the desktop version. And there are only a few rows, and the difference is visible.