Forum Discussion

majidmx's avatar
majidmx
Frequent Visitor
11 months ago
Solved

PowerBI export not exporting all rows.

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 a...
Need Help
  • v-hjannapu's avatar
    v-hjannapu
    11 months ago

    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-desktop

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

rohit1991's avatar
rohit1991
Super User
11 months ago

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's avatar
    majidmx
    Frequent Visitor
    11 months ago

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

    Regards

    MMx

    • rohit1991's avatar
      rohit1991
      Super User
      11 months ago

      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.

    • v-hjannapu's avatar
      v-hjannapu
      Community Support
      11 months ago

      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.

  • majidmx's avatar
    majidmx
    Frequent Visitor
    11 months ago

    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