cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hendustu
New Member

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?

 

1 ACCEPTED SOLUTION
curth
Power BI Team
Power BI Team

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

View solution in original post

15 REPLIES 15
karaoan
Kudo Commander
Kudo Commander

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

 

Martin

jbulk
Regular Visitor

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.

😎 

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()

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

mim
Advocate V
Advocate V

another option that does not require any third party is to use powershell, i tried the vba approach and it does not work with large data, R works very well, but in some environement it may not be available

 

the script is here

 

 

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!

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

fbeekvel
Frequent Visitor

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.

ImkeF
Super User
Super User

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/

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

konstantinos
Memorable Member
Memorable Member

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/
Konstantinos Ioannou
curth
Power BI Team
Power BI Team

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

View solution in original post

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Hi,

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

 

Regards, 

Mohamed. 

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. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors