Edit 03 Aug 2016: With the July 2016 release you can now run your R-scripts directly in the query-editor (so no row-limit any more!). No need to go via the visuals. But this will limit you to export datasets from the query-editor, so no DAX.
Edit 22 Jan 2016: Currently this method is limited to 150k rows! It will drop all others without warning!
Edit 25 Jan 2016: 150k rows is the limit on all R-scripts at the moment (link). A warning sign will be shown on the image. Will create a new blogpost if this changes – so stay tuned (by following this blog, Twitter or LinkedIn)
With the December release, Microsoft enabled Power BI’s content to be fetched from the R-feature. So instead of plotting this data to the screen, could we just export it to a file?
Yes we can:
write.table(trim(dataset), file=”your filepath & filename.txt”, sep = “\t”, row.names = FALSE);
write.table has a lot of options, which are explained here. I’ve only used a few here: Exactly defining the location where the output-file shall be stored (file=), setting tab as delimiter (sep=”\t”) and skipping row names, which would be an index in our example here.
In addition to that, I had to get rid of trailing whitespaces that somehow sneaked into the table (trim(dataset)). Therefore the gdata-package is needed. So the full R-code looks like this:
write.table(trim(dataset), file=”your filepath & filename.txt”, sep = “\t”, row.names = FALSE)
Here you have to make sure to turn the slashes in the filepath: So if your path looks like this: C:\Users\Imke\Desktop\Rfile.txt you need to write it like this: C:/Users/Imke/Desktop/Rfile.txt
Let’s take this as a start that will probably fulfill many users need to quickly export data from Power BI to simple text files. Further use cases I see are:
- Workaround for export of Power Query result with over 1,1 Mio rows (import your Excel-Power-Queries into Power BI).
- Solve performance problems that arise from too complicated transformations: Now we can buffer temporary results in external files and re-import them, breaking the refresh chain
- Incremental load scenarios: If we have data that grows with time and the transformation before loading into the model takes quite some time, it could make sense to perform the transformations only once – then loading results to the external files and fill the datamodel with an append: transformed newest data and “just imported already transformed” data from the external file(s). Here a further option could come in very handy: “append=TRUE” will append the exported data instead of overwriting the file. Just add column.names=FALSE, because you only need them once.
- Web-harvesting: Keeping data that will be replaced by newer data with next download: See above
But watch out: These R-features are still in preview mode, so not safe to use in production.
Export to SQL-server? – worth checking out.
Are you new to R and fear effort or trouble of installing? No need at all – Just install R with standard settings (1), set options in Power BI (1), install R-Studio with standard settings (2) and install gdata-package (2). Shouldn’t take more than 15 minutes:
- How to download and activate R-features in Power BI: here
- Easiest way to install packages is via R-Studio:
Then you start a new visual for the Export: Choose R-visual and check the fields that you want to see in your table.
This will create the dataset, that the R-script references.
For further details on how to get started with R-visuals, check out Dustin Ryan’s excellent blogpost.
Edit 2015-01-04: If you’re using PBI service, you can export your data to csv directly. But this is currently limited to 10k rows.
Edit 2015-01-12: Just found this brilliant comprehensive guideline of “How to Learn R”: With learning paths and many resources!
Edit 2017-03-11: Here you’ll find a detailed description on how to export to SQL-server.
Enjoy and stay queryious 🙂