Gist d2f55e96546318a1e8e393d423ea36e9

100%

Gist d2f55e96546318a1e8e393d423ea36e9

✓ Published0🌍 Public

Last edited Mar 5, 2018

Created on Mar 5, 2018

This example shows a SQL query that transfers data from one Excel sheet to another, creating a new table in a separate workbook file. The visualization demonstrates how data can be exported or copied between spreadsheet locations using an embedded query. The code uses the `SELECT * INTO` syntax with an `[Excel 8.0;DATABASE=...]` connection string, specifying the destination file path and sheet name. The query pulls all records from the source sheet and writes them to the target workbook, illustrating a straightforward data-migration pattern. The rendering approach relies on the database driver’s handling of the Excel file format, with no additional charting or graphics libraries involved.

AI-generated description

Similar vizzes

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

69% match

Loading thumbnail…

Gist 47d998760fa7271ac3157ce7d9555612

This example demonstrates how to query and load data from external sources using Power Query's OleDb.Query function, specifically connecting to a local CSV file and an XML catalog. The code shows two approaches: one using Microsoft's ACE OLEDB provider to read a delimited text file, and another using the MSDAOSP provider with MSXML2.DSOControl to access XML data. The visualization highlights the raw query definitions rather than rendering graphical output, focusing on the data-acquisition layer. Both queries reference file paths on the local system, with the CSV query specifying the file directory and the XML query pointing to a catalog file.

HHugoberry

69% 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

66% match

Loading thumbnail…

Gist fad134c9dcebee3dc1c56a9cb22de435

This example demonstrates a Power Query M function that serializes a table into a JSON object and then passes that JSON to a SQL Server stored procedure, `usp_InsertDemoJSON`, which unwraps it as a recordset and performs an INSERT operation. The code uses `Text.FromBinary` and `Json.FromValue` to convert the table, then `Value.NativeQuery` to execute the stored procedure with the JSON payload. It shows a data pipeline from a Power BI or Excel data source into a SQL database, relying on the M language’s native JSON handling and SQL Server’s JSON support.

HHugoberry

64% 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

64% match

Loading thumbnail…

Gist be32a2ac117700d3dbde6b0824367ba5

This example demonstrates how to create a simple database table visualization from a SQL schema definition. The code defines a `Demo` table with three columns—`ID`, `Key`, and `Value`—and shows how to map those column types and constraints into a visual entity-relationship style diagram. Using the provided `CREATE TABLE` statement as the data source, the visualization renders each column as a node, with the primary key and nullable attributes indicated through distinct visual encodings. The implementation leverages `d3.forceSimulation` for the node layout and `d3.geoPath` for any geometric rendering, while relying on standard DOM elements for interactivity. No additional behavior beyond the table’s structural representation is implied.

HHugoberry

62% match

Loading thumbnail…

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

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.

HHugoberry

62% match

Loading thumbnail…

Power Query embedded in an excel odc connection file

This example shows how a Power Query formula is embedded inside an Excel ODBC connection file, specifically within a Mashup.xml document. It demonstrates the structure of a single query item that generates a simple list of numbers from 1 to 20 using the `let` and `in` syntax. The code, written in Power Query’s M language, defines a source as a list range and then outputs that list. The file includes metadata such as client type (EXCEL), version, and culture settings, and the query is stored as a CDATA block within the XML, allowing for direct execution by Excel’s data mashup engine.

HHugoberry

62% match