Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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
Thank you for the update psolli, I will give it a look and try.
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
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.
Best Regards,
Herbert
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?
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |