Forum Discussion

emohammad's avatar
emohammad
Frequent Visitor
4 years ago

Export data more than 150k rows in .xlsx

Hi,
I would like to check there is any way to overcome the limitation to export data of more than 150k rows from power BI using underlying data. Also, I would like to check if this could be possible by using the premium capacity workspace.

Thanks,

Eliyaz.

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    Yes, in Premium scenarios you can use "Analyze in Excel" which doesn't have that limitation.  Or you can craft your own DAX query and pull the data that way.

  • emohammad's avatar
    emohammad
    Frequent Visitor
    4 years ago

    Hi,

    Thanks for your reply!

    I am using a premium capacity workspace, When I export data through "Analyze in Excel", I am getting the below error when I tried to see the data. 

     

    Can you please share how we can export full data by using DAX?
    When I explored on internet, I came to know that it can be done on DAX studio (https://daxstudio.org/). I am not sure if it is an authorized Microsoft Product or Service. My data has sensitive information, so not sure if it is reliable to use DAX Studio. If yes, can you please share the specific steps to export full data from Power BI or if you have any better alternative?
     
    Thanks,
    Eliyaz.
    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      4 years ago

      DAX Studio is just one of the tools to access XMLA endpoints (albeit a very good one).  You can use whatever you feel comfortable with, including SSMS (above18.8), Tabular Editor, ALM toolkit etc.

       

      The most simplistic way to extract a table from a Power BI dataset is  run this DAX query:  "EVALUATE 'TableNameHere'".  Often you don't want the entire table (yours seems to suffer from column bloat) so you can tailor the DAX to group and filter.

       

      If you need to "export full data from Power BI"  then you are doing something wrong.  That's not what Power BI is for.  Export from the Power BI data source instead.

       

       

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, emohammad ;

    Exporting through DAX Studio is ok and you can even export your whole model at once: https://xxlbi.com/blog/dax-studio-export-all-data/ 

     

    https://www.biinsight.com/exporting-data-from-power-bi-desktop-to-excel-and-csv-part-1-copy-paste-and-dax-studio-methods/

    https://stackoverflow.com/questions/51375021/export-data-from-power-bi-to-sql-server-overcome-the-limit-of-150k-rows


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi， emohammad ；

    Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we could close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

    Best Regards,
    Community Support Team_ Yalan Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.