PowerBI export not exporting all rows.

[majidmx]

I have a power bi report that contains 135 rows. When I export the visiual table it is only exporting 116 rows to excel. If I look at the table in Data view and copy and paste to excel all 135 rows are present.

We have no filters or aggregations applied.

I have allowed undelying data to be exportted in the report settings but its still not working.

Please help.

[majidmx]

Hi all Thanks for the replies. If I add a unique ID field then yes it exports all the rows. However we are using PowerBI to create a transformed export file so the added ID column would then need to be deleted out, thus the automation won't work as expected as the user has to delete a column from the export. Is there any other work around or solution. I tried allowing users to export summrised and underlying data from the options but that has not resolved.

Regards
Majidmx

[v-hjannapu · Accepted solution]

Hi @majidmx,
Thank you  for reaching out to the Microsoft fabric community forum.

The reason you see fewer rows in export is because Power BI groups rows in a table visual when all column values are same. That’s why adding a unique ID shows all 135 rows.

Right now, there is no other setting that will stop this grouping when you export from a visual. The “export summarized and underlying data” option only helps when you choose Export > Underlying data from the Service, it will not change the table visual export.

So, try these :

Use Export underlying data if enabled by admin this will give you all rows without adding ID.

Or keep using the table visual export with an ID column and remove that column later.

Kindly refer to the below documentation links for better understanding:
https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-export-data?tabs=powerbi-d...

Hope this helps if you have any queries we are  happy to assist you further.
Regards,
Community Support Team.

[v-hjannapu]

Hi @majidmx,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Harshitha.

[majidmx]

Thank you for the replies thus far. Adding the indexing column work, however what we are doing is using PowerBI to create an export file in a certain format which does not have a unique ID field. Some of the reports have invoice numbers so I presume those reports won't have any issues. Are there any workarounds apart from indexing?

Regards

[DataNinja777]

Hi @majidmx ,

This is a common Power BI behavior, and the discrepancy arises because your table visual is summarizing or aggregating your data. The Data View shows you the raw, un-aggregated 135 rows exactly as they are in your model. However, when you create a table visual on the report canvas, Power BI automatically groups rows that have identical values across all the columns you've chosen to display. This grouping is why you see 116 unique rows in the visual instead of the full 135.

The most effective solution is to force the visual to treat every row as unique by adding a unique identifier column to the visual itself. This prevents Power BI from being able to group any of the rows. If your data already has a column with a unique value for every row, like a transaction ID or a primary key, you can use that. If not, you can easily create one in the Power Query Editor. To do this, click on Transform data, select your table, go to the Add Column tab, and click Index Column. This adds a new column with a unique number for each row.

After ensuring you have a unique column, return to the report view and select your table visual. Drag the unique ID or Index column from your Fields pane into the Columns bucket for the visual. By default, Power BI might try to sum this new numeric column. You must change this behavior. Click the small dropdown arrow next to the column's name in the Columns bucket and select the Don't summarize option. This action guarantees that every single row from your underlying data is displayed individually in the visual, allowing you to export all 135 rows to Excel correctly.

Best regards,

[rohit1991]

Hi @majidmx 

Export uses the visual’s query, not the raw table. If your table visual doesn’t include a unique row key, Power BI groups identical rows (same values in the shown columns), so fewer rows get exported (116 vs 135). No filters are needed for this to happen.

How to get all 135 rows:

• Best: Export Underlying data (Service >> Export >> Underlying data; requires Build permission + “Allow export underlying data”).

• Or: Add your table’s Row ID/primary key to the table visual (set to Don’t summarize; you can hide it by shrinking the column) and then export.

[majidmx]

Thanks for your request. I am trying to find the setting in the PowerBi Admin Center but  I don't see the option. Am I looking in the wrong place?

Best regards

Majidmx

[majidmx]

Where can I find this option? Is it in report settings in PowerBi Desktop? 

Regards

MMx

[v-hjannapu]

Hi @majidmx,
This option is not available in Power BI Desktop. The setting you see there only takes effect once the report is published. To get the underlying data export option, you have to check it in the Power BI Service. After publishing the report, open it in Service and go to Export Data. If the dataset owner has enabled “allow users to export underlying data” in the dataset settings and you have Build permission on that dataset, then you will see the option. If it is still missing, then it is possible that the export of underlying data is restricted by your admin at the tenant level.

Regards,
Community Support Team.

[rohit1991]

Hi @majidmx 

File >> Options and settings >> Options >> Current file >> Report settings >> Export data
Select “Allow end users to export both summarized and underlying data …” and republish.

Also required in the Service:

• Admin must allow Export underlying data in Admin portal >> Tenant settings >> Export data.

• The user exporting needs Build permission on the dataset.

After these are set, you’ll see Export >> Underlying data in the Service.

[Shahid12523]

Your Power BI visual likely groups rows during export, causing fewer rows to appear. To fix this, add a unique ID column to the table visual to avoid implicit grouping. Also, ensure “Show items with no data” is enabled and export the underlying data (not summarized). For full rows, try “Analyze in Excel” or copy directly from Data view.