将数据从 Power BI 导出到 SQL Server 突破了 150k 行的限制 [英] Export data from Power BI to SQL Server overcome the limit of 150k rows

问题描述

有没有办法克服 Power BI 导出最大 150k 行的限制?

限制文档:

它将整个M表导出到SQLEXPRESS(或您提供的任何SQL Server)到数据库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 the Power BI export max limit of 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

解决方案

It is possible to export data from Power BI to SQL Server without any limit. You should be familiar with R and SQL Server to do that. The example below exports 201k rows directly from 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 the 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 the 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 a 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 导出到 SQL Server 突破了 150k 行的限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！