How to export more than 150000 rows from Power BI Dataset/Dataflow in Power BI Service

[darshaningale]

I have a requirement of creating Power BI Dataset or Power BI Dataflow in Power BI service.

The end user should be able to export more than the limitation of 150000 rows from Power BI Service.

How it can be done ?

[v-cazheng-msft · Accepted solution]

Hi @darshaningale,

If you would like your end users to export data more than 150,000 rows from Power BI service visuals, sadly,  which is not supported by Power BI for now. And it has been listed as an limitation in official document. Considerations and limitaions for export data from a Power BI visualization - Power BI | Microsoft D.... Here is a workaround you may take. You could export your data with the help of filters such as Slicers, which means you could export parts of the data in each export.

In addition, you could consider voting for the following ideas in Power BI Ideas as well. It’s a place where the product group collects suggestions from the users to improve this product.

https://ideas.powerbi.com/ideas/search-ideas/?q=export%20more%20than%20150000%20rows

https://ideas.powerbi.com/ideas/idea/?ideaid=47820719-f315-48b1-b708-146c2d0e4db8&page=1

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[stevebolton · Accepted solution]

I realize this is an old post but maybe you are still looking for the solution.  It sounds like you are trying to get a full export of data from a PBI semantic model for use in Excel.  There  is a connector in Excel Power Query that allows using a PBI semantic model as a datasource.  This would give you access to the tables (but not the measures) in your source PowerBI report.

Hope this is helpful.

[v-cazheng-msft · Accepted solution]

Hi @darshaningale,

If you would like your end users to export data more than 150,000 rows from Power BI service visuals, sadly,  which is not supported by Power BI for now. And it has been listed as an limitation in official document. Considerations and limitaions for export data from a Power BI visualization - Power BI | Microsoft D.... Here is a workaround you may take. You could export your data with the help of filters such as Slicers, which means you could export parts of the data in each export.

In addition, you could consider voting for the following ideas in Power BI Ideas as well. It’s a place where the product group collects suggestions from the users to improve this product.

https://ideas.powerbi.com/ideas/search-ideas/?q=export%20more%20than%20150000%20rows

https://ideas.powerbi.com/ideas/idea/?ideaid=47820719-f315-48b1-b708-146c2d0e4db8&page=1

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[Burningsuit]

Hi @darshaningale 

You may be able to do this with the new DataHub preview download. Like the new Formatted tables it looks like this is using Paginated Reports under the hood, and as there is no limit to the number of rows you can export in Paginated Reports this may allow more than 150,000 rows. Of course if you export to Excel you'll have the excel row limit, but exporting to CSV may go beyond that. 

Note, I haven't exported this many records myself (why the heck would you want to do that? just access the original data) But I have pieced the answer together from these resources.

Data discovery using the data hub - Power BI | Microsoft Docs

Preview data is now available in the dataset details page | Microsoft Power BI Blog | Microsoft Powe...

Exportable Formatted Data Tables (Preview) | Microsoft Power BI Blog | Microsoft Power BI

Yes, you can export unlimited** rows of data from Paginated Reports in Power BI – Christopher Finlan

Hope this helps

Stuart

[fcerullo]

*(why the heck would you want to do that? just access the original data)

Because I need to further enrich and shape the data that comes from more then one Dataset. I I may not have access to the original data.

In my case, I have Inventory data from 3 sources, each organized slightly differently. (I.E. On Hand is in one Dataset, Distributor Inventory is in another, and Purch Orders in a 3rd. I make Reports for Operations and also for Finance, and they each define what they consider to be "Inventory" differntly. Like, Finance counts POs that have not been recieved yet as Inventory on the books, but, Operations would not consider that since they can't use it in production yet).  

If I was able to get all 3 Datasets into PowerQuery, I would do my merges and appends there, and end up with a nice neat Fact table, perhaps with a column called 'source', so I could categorize my Inventory, either in PQ or DAX.  I envision a series of if statements, like "FinInv = if source is Dataset 1 or Dataset 3 then 'Dataset 1' [Qty] + 'Dataset 3" [Qty] else 0" and then "OpsInv =   if source is Dataset 1 then 'Dataset 1 [Qty] else 0"  

When I try to append them in DAX, I hit a 1Mill row limit pretty quick

[fcerullo]

Edit:

Another scenario:    Even when I do have access tot he source data, the format of that soruce frequently chanegs, they add and remvoe columsn form the Excel sheets, the SharePoitn folders get moved and renamed.  But, the PowerBI reprots are pretty stable and rarely have new columns added.  So, it provides more consistent output for me to use as input in my process.                I guess I just wish I could link my pbi to the Viz  import mode, as opposed to the Queries and Measures in DQ.  

[darshaningale]

I need to provide a solution through Power bI Service where the user will be able to download data which has more than 150000 rows.