Forum Discussion

hendustu's avatar
hendustu
Icon for Advocate I rankAdvocate I
11 years ago
Solved

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 C...
  • curth's avatar
    curth
    11 years ago

    Consider making a feature request on the "Give Feedback" site.

jbulk's avatar
jbulk
Icon for Advocate I rankAdvocate I
9 years ago

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)
  • ozmike's avatar
    ozmike
    Icon for Resolver I rankResolver I
    8 years ago

    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.

     

  • Gozde's avatar
    Gozde
    Icon for Helper I rankHelper I
    9 years ago

    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

    • ImkeF's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      9 years ago

      I would simply add it to the dataset itself: Add a custom column with: DateTime.LocalNow()