topic Re: Can PowerQuery write data back out? in Power Query

Can PowerQuery write data back out?

hendustu — Tue, 28 Jul 2015 17:14:35 GMT

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?

Re: Can PowerQuery write data back out?

Greg_Deckler — Tue, 28 Jul 2015 17:30:36 GMT

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

Re: Can PowerQuery write data back out?

hendustu — Thu, 30 Jul 2015 14:15:18 GMT

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.

Re: Can PowerQuery write data back out?

curth — Fri, 31 Jul 2015 14:38:12 GMT

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

Re: Can PowerQuery write data back out?

hendustu — Fri, 31 Jul 2015 15:08:00 GMT

Thanks! If it gets approval, you can find it here:

https://support.powerbi.com/forums/265200-power-bi/suggestions/9111658-allow-power-query-to-export-data

Re: Can PowerQuery write data back out?

konstantinos — Fri, 31 Jul 2015 18:26:55 GMT

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/

Re: Can PowerQuery write data back out?

ImkeF — Mon, 28 Dec 2015 19:44:19 GMT

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/

Re: Can PowerQuery write data back out?

fbeekvel — Fri, 08 Jul 2016 18:41:10 GMT

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.

Re: Can PowerQuery write data back out?

mim — Sat, 31 Dec 2016 07:01:55 GMT

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

Re: Can PowerQuery write data back out?

ImkeF — Sat, 31 Dec 2016 07:50:44 GMT

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!

Re: Can PowerQuery write data back out?

Moh_ali_square — Mon, 24 Apr 2017 09:47:21 GMT

Hi,

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

Regards,

Mohamed.

Re: Can PowerQuery write data back out?

jbulk — Tue, 15 Aug 2017 05:44:05 GMT

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)

Re: Can PowerQuery write data back out?

Gozde — Wed, 06 Sep 2017 07:21:39 GMT

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

Re: Can PowerQuery write data back out?

ImkeF — Wed, 06 Sep 2017 17:56:35 GMT

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

Re: Can PowerQuery write data back out?

ozmike — Wed, 14 Feb 2018 05:10:03 GMT

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.

Re: Can PowerQuery write data back out?

karaoan — Mon, 25 Mar 2019 10:19:19 GMT

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

Martin