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




About this tutorial:

Video duration: 6:48
Do you want to export your power query data? In this video I show you how to do it 🙂

How to install R:
BI accountant blog:
Link to Power BI file:

R script:
require(gdata)
write.table(trim(dataset), file=”C:/writeyourfilepathhere/filename.txt”, sep = “t”, row.names = FALSE)
plot(dataset);

Looking for a download file? Go to our Download Center:

SUBSCRIBE to learn more about Power and Excel…

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

16 thoughts on “Download/ Export Power Query data to csv, excel or txt – Power BI Tips & Tricks #25

    Juan Ariel Fernández

    (September 11, 2019 - 3:02 am)

    Buenos días Ruth, estos pasos son los que necesito para exportar a excel? Por lo que veo tu vacías la tabla 🙁
    Sucede que mis usuarios necesitan visualizar en un xls la informacion relacionada a una tabla especifica.
    Que puedo utilizar?
    Muchas gracias!

    Nicolas Ulcigrai

    (September 11, 2019 - 3:02 am)

    Hello Ruth, can I write a csv file directly to share point/ Overdrive folder? Or only this solution works locally. Thanks

    Barbara Epifanova

    (September 11, 2019 - 3:02 am)

    Dear Curbal, I am now also looking for a solution of how to set up automatic export from POWER Bi Service, do you by any chance know if it is possible? when I uploaded my Dataset with R script – nothing happens on refresh, while on Desktop it works as a magic..
    Thank you so much! really great that you exist, your videos have helped me out so many times 🙂

    Barbara Epifanova

    (September 11, 2019 - 3:02 am)

    Dear Ruth, is there a possibility to make the file name dynamic?
    For example, to add a number to the export file?

    BH

    (September 11, 2019 - 3:02 am)

    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!

    ragu vedula

    (September 11, 2019 - 3:02 am)

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

    krishnakishore peddisetti

    (September 11, 2019 - 3:02 am)

    Can you create a video on how to export data using python script.

    That Will be really helpful

    Thanks and Regards
    Kishore

    Seif Fathalla

    (September 11, 2019 - 3:02 am)

    I get the following error, can anyone help ? thank you

    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':

    nobs

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

    object.size

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

    startsWith

    Error: unexpected input in "write.table(trim(dataset), file=”"
    Execution halted

    Details:
    DataSourceKind=R
    DataSourcePath=R
    Message=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':

    nobs

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

    object.size

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

    startsWith

    Error: unexpected input in "write.table(trim(dataset), file=”"
    Execution halted

    ErrorCode=-2147467259
    ExceptionType=Microsoft.PowerBI.Radio.RScriptRuntimeException

    Kiran B Chitari

    (September 11, 2019 - 3:02 am)

    I want to export one table into excel but the formatting of table is removed when exported to excel. How we can preserve the formatting of tables.

    tera pati

    (September 11, 2019 - 3:02 am)

    Hi, thanks for your tutorial. I got my api translate from google and I do not know how to use it with a text word and the best plugin to use with. Nobody has a tutorial about this. If you can give me a solution it will be nice. Thank you

    Cloud Hound

    (September 11, 2019 - 3:02 am)

    Wonderful

    Femi George

    (September 11, 2019 - 3:02 am)

    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?

    Rory Neary

    (September 11, 2019 - 3:02 am)

    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.

    Danilo Chavez

    (September 11, 2019 - 3:02 am)

    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?

    Norberto Vera Reatiga

    (September 11, 2019 - 3:02 am)

    Buenos días Ruth, intente desarrollar el ejercicio instale perl y el paquete gdata en R Studio, pero me arroja un error… que inicia con esta información… su orientación seria importante para mi, gracias.

    DataSource.Error: ADO.NET: Error del script R.
    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'

    Deepak Agrawal

    (September 11, 2019 - 3:02 am)

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *