Using R.Execute() in Power Query to export the contents of a table to CSV

100%

Using R.Execute() in Power Query to export the contents of a table to CSV

✓ Published0🌍 Public

Last edited Jun 3, 2017

Created on Jun 3, 2017

This example demonstrates how to use R.Execute() in Power Query to export a table’s contents to a CSV file. The code generates a seed list of numbers from 1 to 1,000,000, converts it into a table, and then passes that dataset to R’s write.csv() function via the R.Execute() API. The R script writes the data to a specified file path. The visualization highlights the integration between Power Query and R, showing how R’s data-export capabilities can be invoked directly within a query pipeline, without additional manual steps.

AI-generated description

Similar vizzes

Loading thumbnail…

Writing 10M lines of a query result to a table in Power Query via R

This example demonstrates how to write 10 million rows of a query result to a CSV file from Power Query by invoking an R script. It shows the integration of Power Query’s table generation with R’s file-export capabilities, specifically using the `R.Execute` function to run `write.csv` on a dataset. The Power Query code creates a list of numbers from 1 to 10,000,000 via `{1..10000000}`, converts it to a table with `Table.FromList`, and passes that table as the `dataset` parameter to the R script. The R script then writes the data to `d:\Downloads\out.csv`.

HHugoberry

83% match

Loading thumbnail…

R.Execute example without the use of R.Execute() in Power Query [behind the scenes this gets executed bin\x64\Rscript.exe" --encoding=UTF-8 RScriptWrapper.R"]

This example demonstrates how to execute an R script from within Power Query without explicitly invoking the `R.Execute` function, instead relying on the `AdoDotNet.DataSource` with the `R.Provider` to run a native query. The code passes a string containing an `output` variable that captures the result of `system2("whoami", stdout=TRUE)`, which retrieves the current username. The resulting table is then converted from binary R data using `RData.FromBinary`. This approach showcases the underlying use of `Rscript.exe` via the provider, highlighting an alternative integration path for R workflows in Power Query.

HHugoberry

72% match

Loading thumbnail…

Reading a Zip file in Power Query via R

This example demonstrates how to read a compressed CSV file directly from a local ZIP archive using Power Query’s R integration. The visualization shows the result of an R script that extracts the file `R_Twitter_sample.csv` from `R_Twitter_sample.zip` and loads it into a table. The Power Query `R.Execute` function runs the R code, which relies on R’s base `read.csv` and `unz` functions to handle the archive and file parsing. The output is then returned to Power Query via the `[Name="datacsv"]` reference, making the data available for further transformation or charting. No external visualization library is used; the focus is purely on data ingestion.

HHugoberry

70% match

Loading thumbnail…

Power Query export data to SQL Server

This example demonstrates exporting data from a SQL Server database by running a large SQL script stored in a local file. It shows how Power Query can leverage native database connectivity to execute a bulk insert operation with high parallelism. The code uses `File.Contents` to read the SQL file, then passes its binary content to `Value.NativeQuery`, which targets a SQL Server instance via `Sql.Database`. Key parameters include `EnableBulkInsert` for efficient data loading, `MaxDegreeOfParallelism` to speed up execution, and explicit timeouts for command and connection. The rendered output is the result set from the query, effectively transferring data into Power BI’s data model.

HHugoberry

70% match

Loading thumbnail…

Exporting data out of Power Query via AdoDb route

This example demonstrates how to export data out of Power Query by directly inserting rows into a SQL Server table through an ADO.NET connection. It uses the `Sql.Database` function to connect to a local server instance and the `Value.NativeQuery` API to execute a parameterized `INSERT` statement, passing literal values for the `@key` and `@value` parameters. The code shows a concrete way to push query results into an external database, bypassing Excel’s native output options. The visible data source is a SQL table named `[dbo].[Demo]`, and the approach highlights ADO.NET’s role in bridging Power Query transformations to persistent storage.

HHugoberry

65% match

Loading thumbnail…

Exporting data out of Power Query into SQL Server via OleDb route

This example demonstrates a direct method for inserting data from Power Query into a SQL Server database by leveraging an OleDb connection. The code constructs a dynamic INSERT statement using `Text.Format`, embedding a numeric value and a string literal into the SQL query. It connects via the `OleDb.DataSource` function with a provider string for SQLOLEDB, targeting a local server and the `QueryPower` database. The approach shows how to bypass more complex data flow layers and write raw SQL directly through the OleDb interface, though it relies on string concatenation for parameter values.

HHugoberry

65% match

Loading thumbnail…

R flavoured Kmeans algorithm in Power Query

This example demonstrates the R-flavored K-means clustering algorithm executed directly within Power Query, using the iris dataset’s petal width and length columns to assign cluster memberships. The visualization shows the resulting iris data points colored by their assigned cluster, with the clustering computed in R via the `kmeans` function and seeded for reproducibility. The Power Query M code invokes the R engine through the `R.Execute` API, passing a script that runs K-means with three centers and twenty random starts, then returns the augmented table containing the cluster labels. The underlying data originates from the built-in iris dataset, and the rendering relies on Power Query’s native table output, not a separate charting library.

HHugoberry

64% match

Loading thumbnail…

STDOUT as data source in Power Query M

This example demonstrates how to capture a command-line program's standard output as a data source in Power Query M. It shows a minimal query that retrieves the current user name by running the `whoami` command, then feeds that result into a table. The implementation relies on the `R.Execute` function to invoke an R script, which uses `system2` with `stdout=TRUE` to capture the command's output, and `read.table` to parse it into a data frame. The resulting table is then returned as the query's output. This approach highlights a practical bridge between operating system commands and Power BI's data transformation layer.

HHugoberry

63% match