PowerBI export not exporting all rows.

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.

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.

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,

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

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