Export PowerBI Desktop and PowerPivit Tables Directly to SQL Tables

[jeckste]

After bringing a lot of data into PowerBI Desktop or PowerPivot models and tables and adding a lot of complex DAX to transform it; I'd like to 'lighten the load' and have the ability to export a table/s directly (no intermediate steps) to a SQL table (insert, update or append).  This will save a lot of time and help to make our analytics more scalable/usable.

Thank you.

[v-piga-msft]

Hi @jojo14，

Sorry for the delay.

If I understand your scenario correctly that you could show the SVG measure in a table successfully when you create the report in Power BI Desktop and it also works fine in Power BI Service. 

However, when you get the data with Power BI dataset connector, you will get error message when use the SVG measure?

If that is your scneario, by my test , I cannot reproduce your issue.

Please check if you have installed the latest version of Power BI Desktop 2.66.5376.1681 64-bit (February 2019).

If you still need help, please share a sample pbix which could reproduce your issue, so that I can have 

Best Regards,

Cherry

[psolli]

Hi.

We have now added support for exporting data from Power Pivot, in Excel or Power BI Desktop, in Power Update. It easy to setup and can be scheduled to run every night.

It also supports Partial Loading of data to SQL.

http://poweronbi.com/2017/06/power-update-from-power-bi-desktop-to-sql-server/

Go to our website, and submit your questions.

Best,

Per Solli

[jeckste]

Thank you for the update psolli, I will give it a look and try.

[mim]

Hi jeckste

did you find any solution, as i am stuck with the same situation,

in my case, i have this massive powerpivot model with a lot of transformation using powerquery and dax, i use  linkback tables in excel to write back the final result in multiple sheet ( workaround for excel 1 million Limitation),  this excel file will be used as a backend for other reports.

it did served me well for the last year, but unfortunately the file keep growing in size, now it is around 400MB, as we are talking about 5 million rows, and by next year the total number will double.

now i am serieusely thinking of ditching the whole thing and start using sql server to do the transformation in the first place, and use powerpivot/powerquery as a front end only for reports.

it is really unfortunate, that microsoft refuse to implement full export data from PowerBI desktop. 

i guess all we can do is vote for this idea

[v-haibl-msft]

@jeckste

You can also try to use R scripts to export data from Power BI back to SQL, please refer to links below:

http://www.thebiccountant.com/2015/12/28/how-to-export-data-from-power-bi-and-power-query/

http://stackoverflow.com/questions/19190744/how-to-quickly-export-data-from-r-to-sql-server

*Note: R visuals in Power BI Desktop has a few limitations.

1. Data size limitations – data used by the R visual for plotting is limited to 150,000 rows. If more than 150,000 rows are selected, only the top 150,000 rows are used and a message is displayed on the image.
2. Calculation time limitation – if an R visual calculation exceeds 5 minutes the execution times out, resulting in an error.

Best Regards,

Herbert

[KGrice]

I don't know that you'll find a way to export directly to SQL from Power BI or Power Pivot. Matt Allington has a detailed post about how you can get the data to into SQL server, but there will be a couple manual steps there, unless you can write custom code to handle that part.

If you would like to see something smoother (and I would, not just for SQL), you can vote on the idea to allow power query to export data. One of the comments there mentions this article as an option, but I don't have the environment to test it out. Maybe if you try it out you'll let us know how it goes?

[jeckste]

Thank you KGrice and others for some input.  I've already read through the posting for Power Query, and there are some good points there.  I believe my request is very different as I've already gone past the process of Power Query and am in the data modeling environment behind Power Pivot and PowerBI Desktop performing type changes and creating many calculated columns with complex DAX. 

I believe this should be considered a separate enahncement request.

To be more specific; while on a table/tab in PowerPivot or PowerBI, create a new ribbon feature/function called  'To SQL'.  This new feature will allow direct create and/or overwrite (all or specific) tables of the given model.  The process would ask for a model name to use as the database name.  It will also prompt the user for which tabs or tables from the model to create in the new database.  The process creates the database, creates the tables (including all calculated columns) and inserts all the records.

Thoughts?

[v-haibl-msft]

@jeckste

I don’t think there is such a powerful function which can achieve your enhancement request directly in Power BI Desktop now. As KGrice said, we may do it with some more manual steps and another way is to vote this idea to make it possible in the future.

Best Regards,

Herbert