Forum Discussion
Advocate ICan PowerQuery write data back out?
Consider making a feature request on the "Give Feedback" site.
Advocate IYou 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)
Resolver IHi
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.