cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

Re: Can PowerQuery write data back out?

Hi,

now you can right click on the table, Select copy table, and then you can past directly in Excel spreadsheet. 

 

Regards, 

Mohamed. 

Highlighted
Regular Visitor

Re: Can PowerQuery write data back out?

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)
Highlighted
Helper I
Helper I

Re: Can PowerQuery write data back out?

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

Highlighted
Super User IV
Super User IV

Re: Can PowerQuery write data back out?

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

Highlighted
Resolver I
Resolver I

Re: Can PowerQuery write data back out?

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.

😎 

Check row counts. Open exported file in power bi, , Transform -> count row in query editor with your original table.

 

Highlighted
Kudo Commander
Kudo Commander

Re: Can PowerQuery write data back out?

@hendustu  the Excel Add-on in  https://www.acterys.com has a feature to load result sets from Power Query in relational tables.

 

Martin

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors