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)
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".
I would simply add it to the dataset itself: Add a custom column with: DateTime.LocalNow()
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Check row counts. Open exported file in power bi, , Transform -> count row in query editor with your original table.