Gist fad134c9dcebee3dc1c56a9cb22de435

100%

Gist fad134c9dcebee3dc1c56a9cb22de435

✓ Published0🌍 Public

Last edited Jan 25, 2018

Created on Apr 10, 2017

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.

AI-generated description

Similar vizzes

Loading thumbnail…

Gist 0ca6276fe1534c7941d45e4bb229cc41

This example demonstrates a SQL Server stored procedure that parses incoming JSON data and inserts it into a relational table. It shows how to use the `OPENJSON` function with an explicit schema definition to extract key-value pairs, mapping the JSON’s `Key` and `Value` fields into corresponding integer and varchar columns. The procedure accepts an `nvarchar(max)` parameter for the JSON payload, making it reusable for dynamic data ingestion. The visualization highlights the transformation from a semi-structured JSON format into a normalized table structure, emphasizing the schema-mapping capabilities of T-SQL’s JSON support.

HHugoberry

78% match

Loading thumbnail…

Gist ba39cf5b6349b2131c577e2e046e8654

This example demonstrates a data-driven approach to XML serialization within the Power Query language, showing how database queries can be wrapped in a custom function. The code defines a let expression that calls the `Value.NativeQuery` function, targeting a SQL Server database named "QueryPower" and executing a stored procedure called `usp_InsertDemoXML` with a binary parameter. The procedure receives an XML payload stored in a variable named `dummy2XML`, which is passed as the `xmlBin` argument. This illustrates a pattern for embedding XML data into SQL Server stored procedures through Power Query, highlighting how native database queries can be integrated into data transformation workflows. The example focuses on the parameter-passing mechanism rather than visual rendering.

HHugoberry

76% match

Loading thumbnail…

Gist faec7eb6c03589ff608bab991699407b

This example demonstrates how to generate and execute a dynamic SQL script in Power Query (M language) to bulk-insert data into a SQL Server table using a table-valued parameter (TVP). It builds a textual TVP declaration and populates it by chunking source rows into sets of 1,000 entries, then constructs a multi-statement script that inserts each chunk and calls a stored procedure. The code relies on M functions like `Table.TransformRows`, `List.Generate`, `Text.Combine`, and `Value.NativeQuery` to programmatically assemble and run the query. The visualization highlights the data flow from a source table to a SQL stored procedure, emphasizing the chunking logic and the use of native query execution.

HHugoberry

74% match

Loading thumbnail…

JSON to Table in Power Query M

This example demonstrates a Power Query M function that converts arbitrary JSON data into a structured table. It shows the step-by-step process of detecting expandable record and list columns, then iteratively expanding them until all nested objects are flattened. The code uses core M APIs such as `Table.ExpandRecordColumn`, `Table.ExpandListColumn`, `List.Accumulate`, and `List.Generate` to handle consistent and inconsistent column types. The source is a JSON object or list, which is transformed into a final normalized table through a loop that checks column types and applies the appropriate expansion method.

HHugoberry

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

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

70% match

Loading thumbnail…

Concept for inserting data into SQL Server

This example demonstrates a concept for inserting data into SQL Server by using Power Query’s M language. It shows a table creation script for a demo table with an identity column, a key, and a value, followed by an M query that iterates over a list of key-value pairs. The code uses `List.Accumulate` to loop through the pairs and calls `OleDb.DataSource` with a SQL `INSERT` statement for each iteration, referencing the SQLOLEDB provider to connect to a local database. The result is the total number of affected records from the inserts, though the actual inserted rows are not visualized or returned in the output.

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

69% match