Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

power bi export to excel

when exporting from power bi to excel i am finding the numbers are not matching between the website and the export.

 

The export says 150,000 records max, but is this record max the maximum amount on the final sum or the individual records going into the sum?


my dataset has about 500,000 rows, which sum into about 77,000 rows. However, in excel the final numbers dont match. they are off by quite a bit too

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    i found the issue, and its a weird one. 

    Basically, i have a time dimension, which has all time periods, except the oldest period because it falls outside of the rolling 12 months.

     

    On the page, i have a time period slicer, which uses the time dimension, and the grid uses the time dimension. When i have no time periods selected, it shows the correct total at the bottom. However, if i manually choose the oldest date, which is not in the time dimension, it filters out the oldest period. that filtered value matches the export. when no periods are selected in the slider, the individual records do not equal the total at the bottom.

     

    So, if i dont have any time periods selected on the time slider (set it to max and min), the total at the bottom shows the full number. However, the records for the oldest period are not in the grid because its outside of the range on the time dimension.

     

    Basically, i think the export is more correct than the grid at this point because the total on the grid is not displaying the sum of the records, its showing the total amount of all records. 

     

    I modified my slider to include all periods, including the oldest period outside of the rolling 12, and it matches exactly now. 

     

    thank you to everyone for the help

7 Replies

  • MariaP's avatar
    MariaP
    Icon for Solution Supplier rankSolution Supplier
    6 years ago

    Hi Anonymous ,

    There are many settings that will influence your export. I am not clear on if you are exporting from a visual / table etc.

    In ancy case take a look at this article for information :

    https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-export-data

     

    Also note some of the Limitations and considerations from this article.

    These limitations and considerations apply to Power BI Desktop and the Power BI service, including Power BI Pro and Premium.

    • To export the data from a visual, you need to have Build permission for the underlying dataset.

    • The maximum number of rows that Power BI Desktop and Power BI service can export from an import mode report to a .csv file is 30,000.

    • The maximum number of rows that the applications can export from an import mode report to an .xlsx file is 150,000.

    • Export using Underlying data won't work if:

      • the version is older than 2016.

      • the tables in the model don't have a unique key.

      • an administrator or report designer has disabled this feature.

    • Export using Underlying data won't work if you enable the Show items with no data option for the visualization Power BI is exporting.

    • When using DirectQuery, the maximum amount of data that Power BI can export is 16-MB uncompressed data. An unintended result may be that you export less than the maximum number of rows. This is likely if:

      • There are many columns.

      • There's data that is difficult to compress.

      • Other factors are at play that increase file size and decrease the number of rows Power BI can export.

    • If the visualization uses data from more than one data table, and no relationship exists for those tables in the data model, Power BI only exports data for the first table.

    • Custom visuals and R visuals aren't currently supported.

    • In Power BI, you can rename a field (column) by double-clicking the field and typing a new name. Power BI refers to the new name as an alias. It's possible that a Power BI report can end up with duplicate field names, but Excel doesn't allow duplicates. So when Power BI exports the data to Excel, the field aliases revert to their original field (column) names.....

    Hope this helps,
    Maria

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Based on my test, it can export 150k rows in table visual which summarized from 300k rows. Could you please try to verify if there are any filter or slicer applied when export to the excel? Does the dataset using Import mode?

     

    Based on this document, The maximum number of rows that the applications can export from an import mode report to an .xlsx file is 150,000.

     

     


    Best regards,

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I am unsure what you mean by import mode report. can that be explained further.

       

      I am trying to export from a data grid with like 5 columns, that is being sliced using a slicer. when i hit export, it gives me a count that is different from the subtotal at the bottom of the grid. 

      • v-lid-msft's avatar
        v-lid-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

         

        We can check the storage mode of each data source from the field panel in the right side:

         

         

        What kind of data you want to export, summarized data or underlying data? Does the exported data greater than the data in table visual or less?


        Best regards,