cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
xprezz Frequent Visitor
Frequent Visitor

No way to export data from PBI above 30 K rows ??

Hello Guys,

Now I've spent many days reading and learning about PBI and also bought books for $250, as it really seems to be a good investment to learn DAX and PBI. Just uploaded a typical CSV file for me : 1,3 million rows and 20 columns. Did some basic filtering and wanted to export the filtered result as it is to be used in an online application as a database. Found out about the 30K limit of exporting the hard way. Now that is is very very bad judgement call from someone to limit export sizes.

 

Understand that there is supposedly some way to export to a cloud - hmm - anyone know how that works ?
I can't get OneDrive to work on my work computer, seems to be limited out somehow.
Possibly could do that on another computer, still not been able to logon there.

 

The other solutions found on the forum are workarounds, such as slicing into chunks of 20K and then reassembling. Obviously a possible one-off but impossible for ongoing basis

 

A BI-solution however nice is rendred worthless if there is no way to export data

Very worried at this point

12 REPLIES 12
xprezz Frequent Visitor
Frequent Visitor

Re: No way to export data from PBI above 30 K rows ??

This effectively blocks out any professional. 150 K rows is also "nothing"

 

Limitations and Considerations

When you export data from a Power BI visual into Excel, keep the following considerations in mind:

  • With a Power BI Pro license, users can export up to 150,000 rows of data for each export. For Power BI Free users, you can export up to 30,000 rows of data.
  • Custom visuals, and R visuals, are not currently supported.

 

https://powerbi.microsoft.com/en-us/documentation/powerbi-service-export-to-excel/

Re: No way to export data from PBI above 30 K rows ??

@xprezz why would you want to take that much data from the presentation layer (a.k.a. powerbi visual) back into a "data source" layout? I don't know about your specific scenario but what I try to do usually is to create PowerBI reports where appropriate and additional Excel reports on the same datasource when necessary. From my point of view a well designed data store is key and can be used with any frontend

xprezz Frequent Visitor
Frequent Visitor

Re: No way to export data from PBI above 30 K rows ??

Well Frank everybody's requirement are ofcourse different. I truely enjoy the ease of use in creating a good visual, but most often in my profession the visuals are used to monitor the overview and then when an interesting anomoly or sitution is discovered the drill down method is used to identify the culprit. So far all this is very standard.

 

Most often we need to filter (by some method or another) a specific BU or transactionperiod and then export a grouping  for further analysis - in excel.

 

To solve the situation rigth now, I will split the file into 2 datasets and load into excel and perform the filter actions and then rejoin the two resulting files. I am in need of 540 K rows of the 1,3 MK rows

Super User
Super User

Re: No way to export data from PBI above 30 K rows ??

If you create an "Export"-query that compresses your data before exporting, you can expand the row-limit by factors up to 10000 (so 3.000.000.000 rows within the standard-edition): http://www.thebiccountant.com/2016/12/06/how-to-store-tables-longer-than-11-mio-rows-in-excel/

(This technique also works for PBI)

pls let me know if you need any help here to implement it for your case.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




xprezz Frequent Visitor
Frequent Visitor

Re: No way to export data from PBI above 30 K rows ??

That was a brilliant solution.

Will have to study your solution and try this. I quite often come across datasets which are much larger than 1,1 MRows. 

 

Once again thank you for sharing such a remarkable solution

 

Happy new year

xprezz Frequent Visitor
Frequent Visitor

Re: No way to export data from PBI above 30 K rows ??

Would be nice to walk me through how to solve the function with the example provided on the web page if you please ?

Highlighted
Super User
Super User

Re: No way to export data from PBI above 30 K rows ??

@xprezz, sorry, I missed your answer here.

 

1) Open the Excel-file from the blogpost

2) Copy the function "fnToJsonTable" to PBI and run it, passing the name of your table as an argument to it

3) Create a visual with that table and export

4) In the Excel-file: Import the exported csv-file (or the excel-table, if you've used PBI service)

5)  Run the function "fnBackToTable", passing the name of the query from 4) to the function

 

Hope this helps, otherwise pls specify where the problem lies.

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




jestevez Frequent Visitor
Frequent Visitor

Re: No way to export data from PBI above 30 K rows ??

The easiest way is by using DAX Studio https://daxstudio.codeplex.com/

 

1. Connect DAX Studio to your Power BI

2. Type EVALUATE yourtable

3. Click Output  --> File

4. Run

 

PS. works for more than 1M rows

Captura1.PNG

ruiromano
Advisor

Re: No way to export data from PBI above 30 K rows ??