克服了Power BI中15万行的导出限制 [英] Overcome the export limit of 150k rows from Power BI

问题描述

是否有一种方法可以克服最大15万行的Power BI导出限制？

限制文档：

它将整个M表导出到SQLEXPRESS（或您提供的任何sql服务器）到数据库 MyDataBase 到表 MyR_table （该表是动态创建的，不必首先在SQL Server上创建）。在我的情况下，它在8分半钟时间内转储了整个201k行的测试表。

链接以供进一步参考：

http://biinsight.com/exporting-power-bi-data-to-sql-server/

https://www.youtube.com/watch?v=ANIZkTZO3eU

Is there a way to overcome Power BI export limit of max 150k rows?

Limit docs:
https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-export-data#limitations-and-considerations

Voting for PBI improvement:
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/18432742-increase-export-data-limits

解决方案

Ok, I got through with that. It is possible. You should be familiar with R and SQL Server to do that. The example below exports 201k rows directly form PBI to SQL Server. Install RODBC package in R. For those who want to do that from scratch, please check the reference links.

Here is an example. Generate a test table in Power BI with 201k rows:

let
    Source = List.Generate(()=>1, each _ < 201001, each _ + 1),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

The table is one Column1 with values starting from 1 to 201001. So beyond PBI limit.

Out with that through R. Menu Transform / Run R Script. Paste the code:

library(RODBC)
conn <- odbcDriverConnect("driver=SQL Server;server=.\\SQLEXPRESS;Database=MyDataBase")
odbcClearError(conn)
sqlSave(conn, dataset, tablename="MyR_table",rownames=FALSE, safer=FALSE, append=FALSE)
close(conn)

It will export entire M table to SQLEXPRESS (or any sql server that you provide) to database MyDataBase to table MyR_table (the table is created dynamically, does not have to be created first on SQL Server). In my case it dumped the whole test table of 201k rows in 8 and half minutes.

Links for further reference:
http://biinsight.com/exporting-power-bi-data-to-sql-server/
https://www.youtube.com/watch?v=ANIZkTZO3eU

这篇关于克服了Power BI中15万行的导出限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！