Download/ Export Power Query data to csv, excel or txt - Power BI Tips & Tricks #25

Do you want to export your power query data? In this video I show you how to do it :)

R script:
require(gdata)
plot(dataset);

SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

ABOUT CURBAL:

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo

#SUBSCRIBE #CURBAL

[DataSpinners]

Hi Ruth, this is really neat. I noticed on the biaccountant link that it was limited to 150k rows, but I did a test and it managed to run for 1.5 million rows. One thing that was a bit tricky was installing the gdata package as I never use R and making sure that the syntax was the right way round (e.g. C:/DAT207x/test2.txt). It really paves the way for some pretty efficient ETL processes.

[deepakagrawal]

Excellent, Ruth. Few months back when this R Script feature was launched in Power BI, I was able to export the data from Power Query without installing Strawberry Perl and by simply using write.table or write.csv. So, just wanted to check what's the need for Strawberry Perl?

Apart from this, Imke Feldmann has also mentioned incremental data load in Power BI using R Script, however, I was not able to practically implement the same in Power BI. Have posted a question also in the same post but guess Imke has not seen that. Therefore, it'd be great if you can do a demo on achieving Incremental Data Loads in Power BI with R Script. It's a must have functionality when we talk corporate databases basis the size/volume consideration and direct query limits the modelling capability in the data model.

[zchase]

if i cannot download or install r/rstudio on my office laptop due to strict security reason, how should i proceed?

[RajaParivlog]

In my office I don't have authority to download Stawberry pearl tool ... is it possible with out the tool can we export the csv file from power bi

[bh]

Hi Curbal! Waving my arms here because I just started using Power BI and have one of the latest versions (Version 2.65.5313.701 64-bit, December 2018) *and it has R visualizations built in*. Also there's now a Copy Table item in the right-click contest menu that I was able to use to copy a cleaned-up dataset (4700 rows) back into Excel. Will still probably download R because I heard it can do some neat stuff, but looks like MS has solved the data export issue!

[efthimiosspiridopoulos]

Hi Ruth, great advice here.
Running that on an existing table though, sends the following error/. Any ideas?
DataSource.Error: ADO.NET: R script error.
Loading required package: gdata
gdata: Unable to locate valid perl interpreter
gdata:
gdata: read.xls() will be unable to read Excel XLS and XLSX files
gdata: unless the 'perl=' argument is used to specify the location of a
gdata: valid perl intrpreter.
gdata:
gdata: (To avoid display of this message in the future, please ensure
gdata: perl is installed and available on the executable search path.)

[ashwinisalunkhe]

Hello Ruth, Thanks for the great video!

[MoodyVideoGraphies]

Thanks Very Informative video. I tried but it doesnt work when your data source is sharepoint folder :(... I dont know why

[manikandanj]

Hi, I have one master excel template. Is it possible to extract specific power bi visual to the master excel template using macro coding?

[reddev]

Thank you very match for the wonderful tutorial, i have a question, what is the role of Strawberry Perl on this video, knowing that we use R langage to export datas? thanks in advance

[raguvedula]

Hi, Happy New Year. this is very Use Full, Can we also Schedule this export on regular intervals ?

[ashwinisalunkhe]

I tried the same but unfortunately, I couldn't follow the same. facing following error: DataSource.Error: ADO.NET: R script error.
Loading required package: gdata
gdata: read.xls support for 'XLS' (Excel 97-2004) files ENABLED.

gdata: read.xls support for 'XLSX' (Excel 2007+) files ENABLED.

Attaching package: 'gdata'

The following object is masked from 'package:stats':


can you please help me to resolve this error?

[garethwoodall]

Hi Ruth stumbled on this video, now you have some python tutorials I wonder if a revisit to this topic with current tips would be on your radar? Best - Gareth

[diegoandradedesouzadias]

Is it possible to be done on Excel's power query?

[danilochavez]

Hola. Tus videos son geniales! Me han servido de mucho para entender como funciona Power BI. Respecto a exportar la data, hay otra opción que considera el uso de Powershell, encontré un tutorial online pero sinceramente no lo entiendo, no soy muy técnico en esto. Podrías considerar hacer un video de esa opción?

[eduardoraro]

Hi Ruth, I'm a big fan of your work and I did as you taught us, but now I'd like to save the CSV file directly to a directory hosted on a company Sharepoint account. How to save this file in the Microsoft cloud without the need for a data gateway? Thank you in advance for the answer.

[memories-fhfd]

Thanks. This is a good video. is there away to invoke this script to save to the file to a cloud location when the report is refreshed in PBI Cloud Service?

[maheshbabu]

Can you tell me how to do the same using Python instead of R

[gagankang]

Do you know how to schedule an export using R script and also setting a query (e.g. data for the last week)

[katielux]

Hello Ruth, Thanks for this video. I realize its a little older so I just wanted to check in with you on it. I am trying to auto export to csv. Can you please tell me if I will be obligated to use a personal gateway (not preferred)? Also, I am having an issue, RStudio will not install "gdata". says it has unzipped it, but then does nothing else. Thanks for the help!