Forum Discussion
Can PowerQuery write data back out?
So I know that PowerQuery is great for consuming and transforming data, but are there any export options? If not, what's the least klugey way to get data out of a BI model and into SQL tables, or a CSV?
Consider making a feature request on the "Give Feedback" site.
15 Replies
You can also add an R script step in the query editor that writes the data set to a csv.
Here is the code.
Transform and load your data with Power BI, M and R | Anthony Bulk | Pulse | LinkedIn
# 'dataset' holds the input data for this script # set the working directory to the location in which you want the transformed CSV saved setwd("C:/temp") # write out the transformed data set write.table(dataset, file="transformed_data.csv", sep = ",", row.names = FALSE) # put the data set into a data frame so that you can continue to work with it. # I did an extra transformation and then built a report off of it df1 <- data.frame(dataset)Hi
Using R to export data out does seem to work.
But here is a step by step approach for those who are not familiar with R (myself).
1) Down load and install R ( probably need admin rights).
To install a MIcrosoft owned version of R click here download
Download the windows version ( will only work with 64 bit power bi).
Alternative, info on getting R
In power bi set up R, FIle -> options and settings -> options -> R scripting
Click on the How to install R link
2) In power bi set up R, choose menu in power bi FIle -> options and settings -> options -> R scripting
set your home R directory to eg. depending on your version browse to
C:\Program Files\Microsoft\R Open\R-3.4.3
also as shown here
3) Select a table you want to write to file in the query editor.
4) Select all column change to text ( optional step). to prevent type conversion errors.
5) Transform -> Run R Script ( if this is greyed out) step 2 failed.
6) Paste in this code. As supplied by jbulk
# 'dataset' holds the input data for this script # set the working directory to the location in which you want the transformed CSV saved setwd("C:/temp") # write out the transformed data set write.table(dataset, file="transformed_data.csv", sep = ",", row.names = FALSE)7) press Ok, try for a small table . There is 1800000 millisec timeout 30 minutes.
I was able to export a table of 2 million rows to CSV.
8)
Check row counts. Open exported file in power bi, , Transform -> count row in query editor with your original table.
Hi Antony,
Thanks for R script, it helped me a lot.
Is there a way to add current date to these script?
So, when I refresh, it will automatically save the date with the day in the title like "transformed_data-06/09/2017.csv".
Regards,
Gozde
I would simply add it to the dataset itself: Add a custom column with: DateTime.LocalNow()
This is brilliant mim !!!
Re size-limitations: Change the export-format to txt (instead of csv) and these size-limits are gone :-)
Thx a ton!
No.
Easiest/quickest way is to go into your PowerPivot model, select the table, Ctrl-A, Ctrl-C. Open new Excel file, Ctrl-V, save as CSV
Yeah, it's just that we're often dealing with very, very large datasets.
There's no way to pull a data model into access is there? I'd say that we could take our workbook and pull it into Tabular Services, but I don't think on-prem SQL Server can refersh Power Query.
Hi,
now you can right click on the table, Select copy table, and then you can past directly in Excel spreadsheet.
Regards,
Mohamed.
Consider making a feature request on the "Give Feedback" site.
Thanks! If it gets approval, you can find it here:
- You can use powerpivot in excel & vba..check this blog post from Kasper de Jonge http://www.powerpivotblog.nl/export-a-table-or-dax-query-from-power-pivot-to-csv-using-vba/
Another option:
With the December update you can use the workaround via R to export your data from Power BI to txt or SQL-server: http://www.thebiccountant.com/2015/12/28/how-to-export-data-from-power-bi-and-power-query/
You can use SSIS to connect to Power BI desktop and use SSIS to export the data to wherever you like.
The method described here: http://biinsight.com/connect-to-power-bi-desktop-model-from-excel-and-ssms/ can be used as well to connect from SSIS.
hendustu the Excel Add-on in https://www.acterys.com has a feature to load result sets from Power Query in relational tables.
Martin
